Re: Fixing Fragmentation

amerar_at_ci.chi.il.us wrote:
>
> Hello,
>
> We have all of our production database under one schema. In order to defrag
> the entire instance (all tables), I figure that I could do a full export,
> then drop user cascade, then import the user.......does this sound correct?

NO! NO! Don't do this -- if you DROP the user you will have to recreate the user before re-importing all of its objects, along with objects that don't require any storage except in the Data Dict. (views, stored procedures etc.) Instead use a SQL statement to generate a script to DROP all *tables* owned by the user (which will also drop the indexes) -- then reimport the user (using parameters FROMUSER & TOUSER) with IGNORE=Y (so you will skip any objects not dropped) & INDEXES=Y, to recreate the indexes on the reorg'd tables. --
Vincent Ventrone
DBA, Dept. R101
vav_at_mitre.org
(781) 271-7048

The MITRE Corp.     |    "...In my opinion there's nothing 
M/S C020            |     in this world beats a '52 Vincent
202 Burlington Rd.  |     and a redheaded girl."
Bedford, MA 01730   |      -- Richard Thompson