RE: Differences between Table Move and table Export
- From: Tim Gorman <tim@xxxxxxxxx>
- To: maheswara.rao@xxxxxxx
- Date: Tue, 23 Sep 2008 10:43:52 -0600
Rao,
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
http://www.EvDBT.com/space_usage.sql 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...
-Tim
Quoting maheswara.rao@xxxxxxx:
[Hide Quoted Text] My apologies for not completely answering the
queries raised by
Jeremiah.
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?
Regards,
Rao
-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxx]
Sent: Tuesday, September 23, 2008 11:43 AM
To: Rao, Maheswara; jeremiah@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059
mark.bobak@xxxxxxxxxxxx
www.proquest.com
www.csa.com
ProQuest...Start here.
Other related posts:
