Printable View
Hi,
Is there any way to make multiple single free extent to a big extent. One of our tablespace has 162 free extents with different sizes (1MB, 5 MB etc..), totally it is around 1G with maximum extent 10 MB.
Any one of the free extent is not being used, because all the segments belogs to that tablespace has 30+ MB as next extent.
It was not done when I coalesced the tablespace, it seems all free extents are not near to each other.
Thanks,
You can avoid this by always specifying extents of same size. Always specify pctincrease = 0 and initial = next. Anyways, To fix this, if you really want to, use export/import(I can think of only this now, maybe someone has any other idea). Like you said, Coaleace wont fix free extents scattered away from each other.
Thanks.
Hi,
Coalesce only union free segments, if possible, but dont free space.
To defrag tablespace, use alter table MOVE or alter index REBUILD.
If do not space in your tablespace, create new tablespace and move all objects.
If do not space in disk, export, drop and import this solution.
Hi Which version of oracle r u using, if its 8i then why dont u create a new local managed tablespace with uniform extents size and move all ur objects to that tablespaces as oracle will take care of
extents management.
regards
anandkl
If your ORACLE version is pre 8i then the process will be somewhat more complicated/ time consuming. I.e. You'll have to create new tablespaces and perform either of the following.
Option a). Create new temporary table from old.
CREATE TABLE temp AS SELECT * FROM emp;
DROP TABLE emp;
CREATE TABLE EMP AS SELECT * FROM EMP
STORAGE(...);
DROP TABLE TEMP
Great GRANTS
Recreate indexes
etc, etc.
A long process.
Option b).
Export Schema (Scott for example)
Recreate Schema objects AND indexes with correct storage sizes. The time consuming process here is recreating the DDL of the objects should you not already have it.
Import Schema back in
Ta da...
Instead of moving all the objects to different tablespace, one temporary approach to solve this is run this sql script which will give the location of segments at the datafile level. Pick up the last object in this result and rebuild in the same tablespace, so that the space unused from the starting of datafile will be now reused. For permamnet solution transfer the data dictionary tablespace to an LMT.
you should give tablespace name as input for this query.
select file_id, block_id, blocks,
owner||'.'||segment_name "Name"
from sys.dba_extents
where tablespace_name =
UNION
select file_id, block_id, blocks,
'Free'
from sys.dba_free_space
where tablespace_name =
order by 1,2,3