RE: Differences between Table Move and table Export

Tim,

Thanks for the great pointers. I will use the space usage package provided by you and arrive at correct conclusions.

Once again, thanks a lot for showing the correct way.

Regards,

Rao

-----Original Message-----
From: Tim Gorman [mailto:tim_at_evdbt.com] Sent: Tuesday, September 23, 2008 12:40 PM To: Rao, Maheswara
Cc: Mark.Bobak_at_proquest.com; jeremiah_at_ora-600.net; oracle-l_at_freelists.org
Subject: RE: Differences between Table Move and table Export

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:

> 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.
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
> maheswara.rao_at_ubs.com
> Sent: Tuesday, September 23, 2008 11:21 AM
> To: jeremiah_at_ora-600.net; oracle-l_at_freelists.org
> Subject: RE: Differences between Table Move and table Export
>
> Thanks Jeremiah.
>
> Oracle version: 9i
>
> Regards,
>
> Rao
>
> -----Original Message-----
> From: Jeremiah Wilton [mailto:jeremiah_at_ora-600.net]
> Sent: Tuesday, September 23, 2008 11:11 AM
> To: Rao, Maheswara; oracle-l_at_freelists.org
> Subject: RE: Differences between Table Move and table Export
>
> maheswara.rao_at_ubs.com wrote:
>
>> We have tables that are highly fragmented. We are planning to reorg
> these
> tables...
>> Our aim is to reclaim the disk space and reset HWM.
>>
>> If we use Alter table move, does the HWM get reset
>
> Yes, alter table move resets the HWM. Between exp/imp and alter table
> move,
> the latter seems far simpler. However, it would be helpful if you
could
> state more clearly what the problem is that you are trying to solve,
> what
> you mean by 'fragmentation,' and your version of Oracle. Depending on
> the
> version, there may be other options available to you that can minimize
> disruption. Please see:
>
>

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.ht
> m#AD
> MIN10161
>
> http://tinyurl.com/4a8emf
>
> Regards,
>
> Jeremiah Wilton
> ORA-600 Consulting
> http://www.ora-600.net
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Please do not transmit orders or instructions regarding a UBS account
by
> e-mail. The information provided in this e-mail or any attachments is
> not an official transaction confirmation or account statement. For
your
> protection, do not include account numbers, Social Security numbers,
> credit card numbers, passwords or other non-public information in your
> e-mail. Because the information contained in this message may be
> privileged, confidential, proprietary or otherwise protected from
> disclosure, please notify us immediately by replying to this message
and
> deleting it from your computer if you have received this communication
> in error. Thank you.
>
>
>
> UBS Financial Services Inc.
>
> UBS International Inc.
>
> UBS Financial Services Incorporated of Puerto Rico
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Please do not transmit orders or instructions regarding a UBS
> account by e-mail. The information provided in this e-mail or any
> attachments is not an official transaction confirmation or account
> statement. For your protection, do not include account numbers,
> Social Security numbers, credit card numbers, passwords or other
> non-public information in your e-mail. Because the information
> contained in this message may be privileged, confidential,
> proprietary or otherwise protected from disclosure, please notify us
> immediately by replying to this message and deleting it from your
> computer if you have received this communication in error. Thank you.
>
>
>
> UBS Financial Services Inc.
>
> UBS International Inc.
>
> UBS Financial Services Incorporated of Puerto Rico
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you.

UBS Financial Services Inc.

UBS International Inc.

UBS Financial Services Incorporated of Puerto Rico

--
http://www.freelists.org/webpage/oracle-l