Re: shrinking space

  • From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • To: Mark.Bobak@xxxxxxxxxxxx
  • Date: Wed, 1 Jun 2011 20:30:01 +0100

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> 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]
> *Sent:* Wednesday, June 01, 2011 1:16 PM
> *To:* Bobak, Mark; 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]
> *Sent:* Tuesday, May 31, 2011 8:31 PM
> *To:* Eugene Pipko; 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™ 4G on AT&T*
>
>
>
> -----Original message-----
>
> *From: *Eugene Pipko <eugene.pipko@xxxxxxxxxxxx>*
> To: *"oracle-l@xxxxxxxxxxxxx" <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: