Re: defragging a tablespace/schema using exp/imp

Verna Legaspi wrote:
>
> Hi,
>
> I'm fairly new to this, but I want to make sure I'm doing the correct thing.
> We just completed upgrading 3rd party software that does a bunch of changes
> to it's schema objects. All of its objects are in one tablespace. No other
> users use this tablespace. The schema has 169 objects after the upgrade.
> Prior to the software upgrade, its tablespace had less than 5 pieces. After
> the upgrade, the tablespace has 117 pieces.
>
> I've made a full DB export & a schema export. I would like to know what's
> the best way to defrag this tablespace. I only know how to delete all the
> datafiles, re-create the database, and do a full import. Surely there's a
> more efficient way to do this if I only care to defrag one tablespace?
>
> TIA,
> Verna

If you have a test instance (and you really should) experiment with the following:

Do an export of the owner(s) of the tables you wish to defrag. Drop the tablespace and then do an import with the fromuser and to user options.

Take the opportunity to look at the storage clauses of the tables. I have found that some 3rd party products create tables with widely differing initial and next extent sizes in the same tablespace. If that is the case create the tablespace with a default storage clause with initial and next extents of the same size and then create the tables using the default storage clause.

You can use the export file to generate the DDL and then edit it by doing an import with something like index or indexfiles=yes. (Sorry, but I don't have the Server Utilities document handy so I can't be precise.)

Also when you do the export be sure to specify compress=n or you will wind up with each table having a different extent size. There is a white paper at www.orapub.com that talks about how to avoid reorganizing your database that explains why this is a bad thing.

Finally, you didn't mention indexes. If they are in the same tablespace as the tables -- move them!

-- 
Jerry Gitomer
Once I learned how to spell DBA, I became one.