De-fragmentation of database

From: Simon Holt <jb85_at_dial.pipex.com>
Date: 1996/09/09
Message-ID: <511v8h$64j_at_soap.news.pipex.net>#1/1

Dear friends,

After a look at our production database, I've decided that some de-fragmentation is in order. Not only that, but a substantial amount of re-organisation of datafile placement and rollback segments is called for.

Before I embark on this epic adventure, I wonder if I could trouble you for some advice: Here is how I propose to go about it...

[Oracle 6.0.37.3.1, HP-UX 9.04]

1:Shutdown database. Perform full cold backup (safety first!)
2:Re-start database in dba mode.
3:Do full export of database with COMPRESS=Y, GRANTS=Y, INDEXES=Y.
4:Drop all tablespaces except SYSTEM and ROLLBACK.
5:Drop all but one rollback segment from ROLLBACK tablespace.
6:Create new rollback tablespace, and create new, carefully planned

(!) rollback segments.
7:Drop last rollback segment from ROLLBACK tablespace, then drop ROLLBACK tablespace.
8:Remove physical files associated with dropped tablespaces (UNIX rm) 9:Re-create all other tablespaces in new locations 10:Import from earlier export .dmp file with IGNORE=Y 11:Take a control file backup, shutdown database, and take another cold backup. Then re-start database in normal mode and log in. 12:Have a cold beer. ;-)

I hope that this will then re-create all the objects in the new structure that I have defined when I created the tablespaces, and not use the original tablespace definitions in the export.

Basically, will this work? Is there anything I have missed? My indexes are currently in tablespaces of their own - will they go back into those tablespaces if I re-create them? Several of the tablespaces have multiple datafiles on several different physical devices - I propose to re-structure it so that each tablespace has a single large datafile on a single physical device; if I re-create the tablespace such that this is the case, will this acheive what I desire?

Many thanks in advance for your time,

                                        Simon Holt.

--
Simon Holt: Systems Designer
Work: (+44) 0117 9823646
Home: (+44) 0117 9562849

Received on Mon Sep 09 1996 - 00:00:00 CEST