Rick Denoire wrote:
> 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.
- Oracle really does NOT care about OS files per se and
neither should you. Oracle primarily cares about tablespaces.
- Why does it matter the name of the tablespace where the tables
and indexes reside? If it does not matter then simply create a
new tablespace & move the objects into it. I see no requirement to
move the object a second time "then back again"
- If you add a new file to the existing tablepsace, stop worrying
about what object is in which file. Simply "reorg" the objects.
Oracle will make them contiguous as long as contiguous free space
exists within the tablespace.
Received on Sun Apr 06 2003 - 10:28:29 CDT
