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