Re: Reorganizing the DB.. the tricky way

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.
>
> 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

Create brand new tablespaces and make them LMT with uniform extent sized to handle the tables that will be moved to them. Then move tables on a selective basis over a period of time.

Daniel Morgan Received on Sun Apr 06 2003 - 13:08:13 CDT