RE: shrinking space

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • To: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • Date: Wed, 1 Jun 2011 15:39:30 -0400

Good point, Niall.  If you have the resources available, by all means, use 
parallel!

From: Niall Litchfield [mailto:niall.litchfield@xxxxxxxxx]
Sent: Wednesday, June 01, 2011 3:30 PM
To: Bobak, Mark
Cc: Eugene Pipko; oracle-l@xxxxxxxxxxxxx
Subject: Re: shrinking space

Pretty fast - parallel dml might well speed up the insert...select and almost 
certainly the index builds..
On Wed, Jun 1, 2011 at 6:23 PM, Bobak, Mark 
<Mark.Bobak@xxxxxxxxxxxx<mailto:Mark.Bobak@xxxxxxxxxxxx>> wrote:
Cool.

To expand a bit on my suggestion, now that I'm at a real keyboard and not on my 
phone......

Create table data_you_want to_keep nologging as select * from target_table 
where <stuff you want to keep>;
Truncate table target_table;
For each index on target_table do:
Loop
Alter index <index_name> unusable;
End loop
Alter table target_table nologging;
Insert /*+ append */ into target table select * from data_you_want_to_keep;
For each index on target table do:
Loop
Alter index <index_name> rebuild nologging;
End loop

You're done.

That should be, by far, the fastest method.

Hope it helps,

-Mark
From: Eugene Pipko 
[mailto:eugene.pipko@xxxxxxxxxxxx<mailto:eugene.pipko@xxxxxxxxxxxx>]
Sent: Wednesday, June 01, 2011 1:16 PM
To: Bobak, Mark; oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: RE: shrinking space

Thanks everyone for your responses.
I have a luxury of having downtime most weekends, so I will CTAS new table. 
That seem to be the simplest approach.

From: Bobak, Mark 
[mailto:Mark.Bobak@xxxxxxxxxxxx<mailto:Mark.Bobak@xxxxxxxxxxxx>]
Sent: Tuesday, May 31, 2011 8:31 PM
To: Eugene Pipko; oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Re: shrinking space

Depending on if you can get maintenance window, I would CTAS new table with 
rows to keep, truncate original table, and then insert rows from temp table 
into empty table. Do as much as possible with nologging.

HTH,

-Mark

Sent from my Motorola ATRIX(tm) 4G on AT&T


-----Original message-----
From: Eugene Pipko <eugene.pipko@xxxxxxxxxxxx<mailto:eugene.pipko@xxxxxxxxxxxx>>
To: "oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>" 
<oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
Sent: Wed, Jun 1, 2011 03:21:03 GMT+00:00
Subject: shrinking space
Hi all,
Oracle 9.2.0.8 on Win 2003.
SAP admitted a bug in their app, which resulted in millions of rows inserted in 
a couple of tables.
They came up with a script to delete them, but failing to provide any solutions 
on how to reclaim unused space.
One of the tables has CLOB column and another has BLOB column in them.

Question: how do I shrink tables with CLOB and BLOB columns?

Thanks,
Eugene




--
Niall Litchfield
Oracle DBA
http://www.orawin.info

Other related posts: