RE: Differences between Table Move and table Export
Date: Tue, 23 Sep 2008 10:43:52 -0600
Message-ID: <20080923104352.cc4hyuco6cccwccw@webmail.evdbt.com>
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_at_ubs.com:
[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_at_proquest.com]
Sent: Tuesday, September 23, 2008 11:43 AM
To: Rao, Maheswara; jeremiah_at_ora-600.net; oracle-l_at_freelists.org
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_at_proquest.com www.proquest.com www.csa.com ProQuest...Start here. -- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 23 2008 - 11:43:52 CDT