RE: Differences between Table Move and table Export

From: Tim Gorman <>
Date: Tue, 23 Sep 2008 10:43:52 -0600
Message-ID: <>


How have you proven that your definition of "fragmentation" (i.e. "lot of unused space in the segment below HWM") is actually occurring?

Have you used the DBMS_SPACE package to look at space utilization in the table and
indexes?  I've posted a wrapper package using DBMS_SPACE at which works for both freelist and ASSM objects, partitioned and non-partitioned.

The results might surprise you, because the scenario you describe does not necessarily sound as if it results in "wastage" of space, neither in the tables nor the indexes, using either freelists (a.k.a. manual) or ASSM.  Of course, I could certainly be wrong, but it doesn't make sense to just guess, if that is what we're doing, does it?

Be aware though, before you use the DBMS_SPACE (or SPACE_USAGE) package that timing is important.  If you look at space utilization immediately after a big INSERT batch job, you might find relatively little "free" space, but a good deal of "unused" (better expressed as
"allocated but not yet used") space.  If you look after a big DELETE
batch job, you might find more "free" space, plus a good deal of that
"unused" space.  Just be aware that your application has these ebbs
and flows, and also be aware of the distinction between "free" space and "unused" space.  The former has been recently freed up by UPDATE or DELETE operations;  the latter has been allocated by extent management but is not yet counted under the HWM.

Hope this helps...



[Hide Quoted Text] My apologies for not completely answering the queries raised by

Our Database version: 9i

The problem we are trying to solve:

The tables we are planning to do reorg have lot inserts and deletes in a day.  On an average, around 250,000 to 300,000 records are inserted into the table in a day.  The average record length is 693 for one of our big table.  Around 150,000 records are deleted in a day.  Updates are done to these tables but I do not have the update stats.  Indexes are defined on these tables.

Due to the above heave I/O activity, lot of disk space is wasted and we want to reclaim the disk space by doing reorg on the tables.

Fragmentation: Lot of unused space in the segment below HWM.

The biggest table size is 125 GB.

We want to move this table into ASSM type tablespace.

Is Alter table move is better or export and import is better?



-----Original Message-----
From: Bobak, Mark [] Sent: Tuesday, September 23, 2008 11:43 AM To: Rao, Maheswara;; Subject: RE: Differences between Table Move and table Export

Hmm...I think you missed the part where Jeremiah asked you what problem you were trying to solve and what you meant by "fragmentation"....

Mark J. Bobak
Senior Database Administrator, System & Product Technologies
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059

ProQuest...Start here.

Received on Tue Sep 23 2008 - 11:43:52 CDT

Original text of this message