| Coalesion on the tablespaces [message #65614] |
Tue, 09 November 2004 00:16  |
Abhijit
Messages: 7
Registered: November 2002
|
Junior Member |
|
|
Operating System - Sun Solaris 2.8
Oracle Database Version - 8.1.7
select TABLESPACE_NAME ,PERCENT_EXTENTS_COALESCED from DBA_FREE_SPACE_COALESCED;
when we run this command logged in as system,it is recommended that all the tablespaces should be 100% coalesced.
The factor coalesion depends on the insert/update & delete from the tablespaces.
The coalesion of the tablespaces goes to less than 40% for many of the tablespaces frequently on weekly basis. If I need to reduce the coalesion on the tablespaces what precautions should I take ?
|
|
|
|
| Re: Coalesion on the tablespaces [message #65615 is a reply to message #65614] |
Tue, 09 November 2004 02:36  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>If I need to reduce the coalesion on the tablespaces what precautions should I take ?
USE LMT ( Locally managed tablespaces) instead of DMT ( Dictionary managed tablespaces).
THere is NO fragmentation. NO coalescation is required.
If you cannot switch to LMT,
always have the tablespaces with these values
pctincrease =0
initial=next = SomeConstantValue
By coalescing oracle will take the adjacent free space and joins into One biggie chunk (of free space).
But the effect of Coalesce will be very less ( less than 5%?? or so). a 100% perfect system is IDEAL.
If you cant do the above ( LMT/pctincrease) the tablespace will got backk to previous again. if so startover.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
