Defragment 9i Oracle Tablespace using alter table move, alter index rebuild, alter table move lob
Posted on 2006-06-16
Using Oracle 126.96.36.199 Enterprise Edition:
I have a fragmented tablespace. I have created a new tablespace.
I have used dba_extents to create queries and scripts to identify segment_types of table, index and lobsegments.
I have executed the following in this order:
1) alter table [table_name] move tablespace [new tablespace] nologging;
2) alter index [index_name] rebuild tablespace [new tablespace] nologging;
3) alter table [table_name] move lob ([lobsegment_name]) store as (tablespace [new tablespace]) nologging;.
As the scripts are processing I can see the new tablespace increase in size/use and the former tablespace decrease i
At the end there is still about 200M left in the former tablespace and I can query dba_extents to identify the segment_name, segment_type.
When I executed the following: drop [former tablespace_name] including contents and datafiles;
the former tablespace dropped including the remaining 200M but nearly all the contents dropped from the new tablespace as well.
1) Why isn't everything being moved from the former tablespace to the new tablespace?
2) Why did the objects now existing int he new tablespace drop when I dropped the former tablespace including contents and datafiles;
I'm able to duplicate everytime. I back up the database before the re-org above, execute the scripts, then restore the database prior to the re-org.