Re: De-fragmentation of database

I would look upon this as a three phase job. I assume you are keeping your tables/indexes in the same tablespaces, and that you are not changing sizes of the tablespaces/datafiles.

1. shutdown your database and move your datafiles to their new locations. This can be done at the operating system level rather quickly. Start up your database and before opening, rename your datafiles as per their new volsers/locations. when everything looks okay you can take a new backup. Run with it for a few days and if you need to make further moves, do so.
2. rollback segment work can be done online while the database is in use, so do this without taking an outage.
3. once you are happy with your I/O balancing (ie datafile placement), do your export/import 'defrag' much as you have described it.

I would not try to do all of it at the same time. We're a 45Gig 7x24 shop and maintenance downtime is precious and comes in small chunks. Too many things to juggle just begs for a visit from Mr. Murphy!

Wayne Linton Received on Tue Sep 10 1996 - 00:00:00 CEST