Reorganizing the DB.. the tricky way

I analyzed the DB at our company (aprox. 180 GB, Oracle 8.1.7) and found out that it is time to reorganize data again: 70% of migrated records (spanning 2 blocks), lots of non-contiguous extents, large fragmentation, index stagnation etc.

Using
"alter table <table_name> move" and
"alter index <index_name> rebuild parallel recalculate statistics
nologging"
gets the segment rewritten but it does not eliminate fragmentation *between* the segments. In order to do that, one would have to take all tables and indexes out of their current tablespace into a kind of scratch tablespace, then back again.

Well, this would take about one week or longer. So I am wondering if I could just add a new file to the same tablespace and move the objects to this file, so they could just stay there and there would not exist any need to move then back. Selecting which objects are contained in one specific file can be done joining dba_extents and dba_data_files through the file_id. But I just don't know how to target a specific file while moving objects.

Using export/import ist not feasible, since this operation lasts even longer and the DB would not be available for work.

Any hint?

Rick Denire Received on Sun Apr 06 2003 - 07:54:01 CDT