Re: Fixing Fragmentation

I disagree. Under older versions of Oracle, I have heard that it was a problem.

Full export
drop user
import full database (not just user)

    Don't use ignore=y, you want everything except the user's objects skipped

    May have to regrant any grants on sys/system objects to the user     (For this reason, take an operating system backup first.)

Vincent Ventrone <vav_at_mitre.org> wrote in message news:3738A938.A6851C_at_mitre.org...
>
>
> 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
Received on Fri May 28 1999 - 00:41:31 CDT