Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: EXP/IMP - Different Space used

Re: EXP/IMP - Different Space used

From: John P. Higgins <jh33378_at_deere.com>
Date: Sat, 26 Dec 1998 15:22:58 -0600
Message-ID: <368553B2.BA16DDC7@deere.com>


This is the expected behavior for export with compress = y.

The compress = y parameter causes export to calculate an initial extent size for a table (or index) that is the sum of all the current extent sizes for that table (or index). It only looks at the allocated extents, not the rows.

In other words, if you inserted many rows into a table and then deleted all the rows, the table will still retain all its extents. If you exported this empty table with compress = y, export still computes an initial extent equal to the sum of all its empty extents. Import creates the table with that size as the initial extent.

On the other hand, export with compress = n does not re-compute the size of the initial extent. Import creates the table with its original initial extent size. If there are no or few rows to insert, import will not allocate added extents and free space remains high.

Sanjay Hans wrote:

I am trying to migrate from Oracle 7.3.3 to Oracle 8.0.4 using full export
backup as system user. I have tried two option for taking the export backup
1) compress=y 2) compress=n.

To my surprise, on importing the export backup (with options compress=y and
compress=n), the space utilisations for the tablespace containing indexes
are different (I have different tablespace for data & index).

The following are after importing the export backup with compress=y
SQL> select sum(bytes),tablespace_name from dba_free_space group by
tablespace_name;

SUM(BYTES) TABLESPACE_NAME
---------- ------------------------------
 291,823,616 INDEX_TAB  ( Tablespace containing indexes)
 610,803,712 DATA_TAB   ( Tablespace containing data )

SQL> select sum(bytes),tablespace_name from dba_data_files group by
tablespace_name;

SUM(BYTES) TABLESPACE_NAME
---------- ------------------------------
 3221,225,472 INDEX_TAB
 2147,483,648 DATA_TAB

The following are after importing the export backup with compress=n

select sum(bytes),tablespace_name from dba_free_space group by
tablespace_name
SQL> /

SUM(BYTES) TABLESPACE_NAME
---------- ------------------------------
1374,478,336 INDEX_TAB
  610,803,712 DATA_TAB

SQL> select sum(bytes),tablespace_name from dba_data_files group by
tablespace_name;

SUM(BYTES) TABLESPACE_NAME
---------- ------------------------------
3221,225,472 INDEX_TAB
2147,483,648 DATA_TAB

The tablespace containing indexes is having only 291,823,616 bytes free on
importing the export backup with compress=y whereas
the tablespace containing indexes is having 1374,478,336  bytes free on
importing the export backup with compress=n.
Please note that the source database (from where export is taken) is also
showing 291,823,616 bytes free only.

Would appreciate, If you can expain the reason for different space
utilisations.

Thanks in advance.

  Received on Sat Dec 26 1998 - 15:22:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US