RE: defragging a tablespace/schema using exp/imp

From: Frederic Bidon <fbi_at_mobilix.dk>
Date: Fri, 28 Jan 2000 00:36:45 +0100
Message-ID: <2D3005375CAED31199D00008C784963F020942@ms02.mobilix.dk>

First try the ALTER TABLESPACE .. COALESCE, to coalesce physically contiguous extents.

If you are using Oracle8 you can MOVE objects accross tablespaces, that's faster and more convenient than the exp/imp. Just try ALTER TABLE ... MOVE ...

Remember that the actual fragmentation is not only a matter of number of extents. If your tablespace spans accross several datafiles, extents will be allocated *at random* (or in a round robin way, dunnow) on the datafiles, so your extents will undergo a kind of striping accross datafiles, which is not necessarily what you want.

Last word : in 8i, use tablespaces with MANAGEMENT LOCAL, so you don't have to worry any more about the number of extents, as they are no more indexed in the main dictionnary.

After that, if you still hang on the exp/imp way, avoid the imp full=y and use a per table import instead or alternatively, a per user import. Syntax ? imp help=y, dear.

-----Original Message-----
From: Verna Legaspi [mailto:verna.legaspi_at_attws.com] Sent: 21. januar 2000 20:53
To: comp.databases.oracle.server_at_list.deja.com Subject: defragging a tablespace/schema using exp/imp

Message from the Deja.com forum:
comp.databases.oracle.server
Your subscription is set to individual email delivery

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

Deja.com: Before you buy.
http://www.deja.com/

To modify or remove your subscription, go to http://www.deja.com/edit_sub.xp?group=comp.databases.oracle.server
Read this thread at http://www.deja.com/thread/%3C86adk3%246vs%241%40redftp.redftp.attws.com%3E

Sent via Deja.com http://www.deja.com/ Before you buy. Received on Thu Jan 27 2000 - 17:36:45 CST