Re: Zero-fill a droped/truncated table/indexes' blocks??

"Andreas Hess" <ahess_at_truworths.co.za> wrote:

>Hi Michael
 

>I think you might be looking for something like
>the ALTER TABLE DEALLOCATE UNUSED command.

  No, no, no. I have had the same problem. Even though Oracle has released the extent it doesn't clean up by re-writing nulls or zeros or whatever. The operating-system does its backup and sees a file with a bunch of data in it. But Oracle is no longer using that data.

   I called World-Wide-Support one time and they told me the only way to do it is to recreate the stinkin' datafile. Thanks, but no-thanks.

>As per the SQL Reference manual:
 

>DEALLOCATE UNUSED
 
>explicitly deallocate unused space at the end of the table and make the
>freed space available for other segments. You can free only unused space
>above the high-water mark. If KEEP is omitted, all unused space is freed.
>For more information, see the deallocate_clause.
 

>Hope it helps
 

>Andreas

>Michael Jessen <Michael.Jessen_at_iName.com> wrote in article
><01bcc3fc$e08c5780$68291ccb_at_internet-4>...
>>
>> I'm looking for a way to 'zero-out' the unused space in a table/index
>> segment thats being truncated or dropped.
>>
>> Why?? A large table is being reorganised into another tablespace, leaving
 a
>> 30Gb hole in one tablespace and another large hole in an index
 tablespace.
>> So our compressed backup (not EBU) will continue to backup this data.
 Since
>> it isnt zeroed out, it wont compress really well. Our backup window is
>> tight so i'm looking for ways to reduce backup volumes.
>>
>> Any suggestions?? So far i could 1) reallocated the released space to a
 new
>> table and fill this with block-sized rows containing a zeroed column. or
>> 2), use EBU.
>>
>>
>> --
>> Michael Jessen
>>

• Doug Anderson Oracle Database Administrator doug.anderson_at_NDCorp.com