Re: Does export/import compress (deallocate) extents used by table ?

In article <1992Aug5.192201.29819_at_oracle.us.oracle.com>, kjou_at_us.oracle.com (Kathy Jou) writes:
> In article <12694_at_inews.intel.com> kortikar_at_mipos2.intel.com () writes:
> >since oracle does not deallocate extents after a delete of say 95% rows from
> >a 50 will export, drop table, import do the trick ?
>
> Yes, but remember to specify compress=y during export so the initial extent
> of the table upon import will be the size of the exported data (ie, compressed
> into one extent).
>
> % exp help=y
>
> The above will give you all the exp command line options and their default
> settings.
>
>
> -- Kathy Jou
> -- Oracle Corporation

I am pretty surprise that an Oracle staff does not know his/her own product. I am afraid that the answer above in not quite true.

First let me explain about export. By COMPRESSING, exp means that it will concatenate or make one single extent based on the data segments being used NOT DATA SPACE being used!!. It does this by setting the INITIAL value of the STORAGE statement to be the sum of total segment bytes.

To be able to 'compress' your extents 'ROWS' must be set to Y, ie you MUST export your data. I am not sure why Oracle has done it this way. It seems silly.

If you set COMPRESS to Y and ROWS to N, you will get the INITIAL size equal to the original INITIAL value of the object (table, index & RBS) when it was created.

Now, if you you have deleted 1/2 your data in the table and export with COMPRESS=Y and ROWS=Y, you will STILL get the full size.

To answer Aniruddha original question, NO. This is not possible in V6 of Oracle. But in V7 I believe that is something planned. It could be similar to the 'OPTIMAL' option for the creation of RBS (rollback segments) in V7. By OPTIMAL, it means that if the RBS grows pass the optimal size and there are no data in the extents beyond the optimal block size, it will truncate/reduce the RBS back to the OPTIMAL size.

So for the time being we have to live with doing it manually ie

(1) dropping the object
(2) recreate the object with minimal initial size
(3) import data
(4) find out its current size
(5) drop the object
(6) recreate the object with the size from step (4)
(7) reimport data

That is my 2 bobs worth of comments anyway. If any one has any other solution I wouldn't mind hearing from you.

-- 
-----------------------------------------------------------------------------
 _____       ________ / ____ |Tony Jambu, Database Administrator
  /_  __       /_ __ /       |Colonial Mutual Life Australia. (ACN 004021809)
 /(_)/ ((_/ \_/(///(/_)/_(   |EMAIL:  TJambu_at_cmutual.com.au