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

From: Jean Anderson <jean_at_seismo.CSS.GOV>
Date: 6 Aug 92 16:22:13 GMT
Message-ID: <51047_at_seismo.CSS.GOV>

In <12694_at_inews.intel.com>, kortikar_at_mipos2.intel.com (Aniruddha Kortikar):
> since oracle does not deallocate extents after a delete of say 95% rows from
> a 50 will export, drop table, import do the trick ?

and

In <1992Aug5.192201.29819_at_oracle.us.oracle.com>, kjou_at_us.oracle.com (Kathy Jou):
> 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).

I wonder if the question is really if Oracle frees up deleted space.

compress=y will reduce extents, but the size of the initial extent will be based on the previously allocated space, not the actual space used. This is great if you plan on filling up that space again, but not so great if you want to free it up.

We routinely run 'actual vs. allocated' reports to see where we can recover deleted space. We 'exp compress=y', 'imp indexfile=create.sql', and alter the initial storage downward in the create script to a more reasonable allocation.

A snippet from last night's report shows massive deletion in one of the tables:

                                          Blocks      Bytes        Bytes  Pct
   Segment Name                Rows  Ext   Alloc      Alloc         Used Used
   --------------------- ---------- ---- ------- ---------- ------------ ----
   WFDISC          DATA       2,874   13    3391    6944768      464,896    7

I did an 'exp compress=y', then an 'imp indexfile=foo.sql' and got:

  REM CREATE TABLE "WFDISC" ("STA" CHAR(6) NOT NULL, "CHAN" CHAR(8) NOT   ...

  REM  PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 6944768 NEXT 10240
                                                          ^^^^^^^

Fiddling around, I also exported the table with compress=y and rows=n, and got:

  REM CREATE TABLE "WFDISC" ("STA" CHAR(6) NOT NULL, "CHAN" CHAR(8) NOT   ....

  REM  PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 4069376 NEXT 10240
                                                          ^^^^^^^

Any idea on how it came up with that initial storage?

 -jean

+-----------------------------------------------------------------------+
| Jean Anderson, DBA                       email:  jean_at_seismo.css.gov  |
| SAIC Open Systems Division, MS A2-F         or:  jean_at_esosun.css.gov  |
| 10210 Campus Point Drive                 phone:  (619)458-2727        |
| San Diego, CA  92121                       fax:  (619)458-4993        |
 +-----------------------------------------------------------------------+
|                     std_disclaimer("mine");                           |
+-----------------------------------------------------------------------+

Received on Thu Aug 06 1992 - 18:22:13 CEST