EXP – COMPRESS parameter in 9i and 10g

The compress parameter in export has been a misunderstood parameter.Even i misunderstood it and related it to compression of data in the export file leading to smaller size of the dump file, until i read this http://oracleadmins.wordpress.com/2008/08/05/understanding-compress-parameter-in-export/where it says

First of all from 10g documentation Utilities Guide:

The default, COMPRESS=y, causes Export to flag table data for consolidation into one initial extent upon import. If extent sizes are large (for example, because of the PCTINCREASE parameter), the allocated space will be larger than the space required to hold the data.

If you specify COMPRESS=n, Export uses the current storage parameters, including the values of initial extent size and next extent size. The values of the parameters may be the values specified in the CREATE TABLE or ALTER TABLE statements or the values modified by the database system. For example, the NEXT extent size value may be modified if the table grows and if the PCTINCREASE parameter is nonzero.

In, nutshell what it means to say is that if we specify COMPRESS=y during export then at the time of table creation while importing, the INITIAL extent of the table would be as large as the sum of all the extents allocated to the table in the original database.

If we specify COMPRESS=n during export then while creating table in the import, it will use the same values of INITIAL extent as in the original database.

Now lets see how it works on 9i and 10g.Does it give us the same result or is it different???So, lets start with 10g,

ANAND @ oracle >create table test_compress as select * from all_objects;

Table created.

ANAND @ oracle >select table_name,INITIAL_EXTENT,NEXT_EXTENT from user_tables where
table_name='TEST_COMPRESS';

TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
TEST_COMPRESS                           65536             

ANAND @ oracle >select sum(bytes)/1024/1024 "MB" from user_extents where
segment_name='TEST_COMPRESS';

 MB  
----------  
 6  

ANAND @ oracle >select count(1) from test_compress;

 COUNT(1)  
----------  
 50027  

ANAND @ oracle >delete test_compress where rownum<10000;

9999 rows deleted.

ANAND @ oracle >commit;

Commit complete.

ANAND @ oracle >
ANAND @ oracle >
ANAND @ oracle >select count(1) from test_compress;

 COUNT(1)  
----------  
 40028  

ANAND @ oracle >select sum(bytes)/1024/1024 "MB" from user_extents where
segment_name='TEST_COMPRESS';

 MB  
----------  
 6  
ANAND @ oracle >
ANAND @ oracle >REM TOOK EXPORT OF THE TABLE "exp file=test_compress.dmp log=test_compress_10g.log
tables=test_compress feedback=1000"
ANAND @ oracle >
ANAND @ oracle >REM COMPRESS parameter is 'Y' by default in exp
ANAND @ oracle >
ANAND @ oracle >REM Will drop the table now and import it back
ANAND @ oracle >drop table test_compress purge;

Table dropped.

ANAND @ oracle >
ANAND @ oracle >REM The table has been imported back
ANAND @ oracle >
ANAND @ oracle >select table_name,INITIAL_EXTENT,NEXT_EXTENT from user_tables where
table_name='TEST_COMPRESS';

TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
TEST_COMPRESS                         6291456

See the initial extent size is 6Mb, which was the size of the original table

ANAND @ oracle >select sum(bytes)/1024/1024 "MB" from user_extents where
segment_name='TEST_COMPRESS';

 MB  
----------  
 6  

ANAND @ oracle >exec dbms_stats.gather_table_stats('ANAND','TEST_COMPRESS')

PL/SQL procedure successfully completed.

ANAND @ oracle >select table_name,round((num_rows*avg_row_len/1024),2)||'kb'
"size" from dba_tables where table_name='TEST_COMPRESS' and owner='ANAND';

TABLE_NAME                     size                       
------------------------------ ------------------------------------------                                                  
TEST_COMPRESS                  3713.54kb                  

ANAND @ oracle >REM Now lets check out with compress=n
ANAND @ oracle >
ANAND @ oracle >drop table test_compress purge;

Table dropped.

ANAND @ oracle >
ANAND @ oracle >create table test_compress as select * from all_objects;

Table created.

ANAND @ oracle >select table_name,INITIAL_EXTENT,NEXT_EXTENT from user_tables where
table_name='TEST_COMPRESS';

TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
TEST_COMPRESS                           65536             

ANAND @ oracle >select sum(bytes)/1024/1024 "MB" from user_extents where
segment_name='TEST_COMPRESS';

 MB  
----------  
 6  
ANAND @ oracle >select count(1) from test_compress;

 COUNT(1)  
----------  
 50027  

ANAND @ oracle >delete test_compress where rownum<10000;

9999 rows deleted.

ANAND @ oracle >commit;

Commit complete.

ANAND @ oracle >
ANAND @ oracle >select count(1) from test_compress;

 COUNT(1)  
----------  
 40028  

ANAND @ oracle >
ANAND @ oracle >select sum(bytes)/1024/1024 "MB" from user_extents where
segment_name='TEST_COMPRESS';

 MB  
----------  
 6  
ANAND @ oracle >
ANAND @ oracle >REM Now took the export using compress=n parameter "exp file=test_compress.dmp log=test_compress_10g.log
tables=test_compress feedback=1000 compress=n"
ANAND @ oracle >
ANAND @ oracle >drop table test_compress purge;

Table dropped.

ANAND @ oracle >
ANAND @ oracle >REM The table has been imported back
ANAND @ oracle >
ANAND @ oracle >select table_name,INITIAL_EXTENT,NEXT_EXTENT from user_tables where
table_name='TEST_COMPRESS';

TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
TEST_COMPRESS                           65536

Now,see the initial extent size is 64Kb which was the initial extent for the original table

ANAND @ oracle >select sum(bytes)/1024/1024 "MB" from user_extents where
segment_name='TEST_COMPRESS';

 MB  
----------  
 5  

ANAND @ oracle >exec dbms_stats.gather_table_stats('ANAND','TEST_COMPRESS')

PL/SQL procedure successfully completed.

ANAND @ oracle >select table_name,round((num_rows*avg_row_len/1024),2)||'kb'"size" from dba_tables where
table_name='TEST_COMPRESS' and owner='ANAND';

TABLE_NAME                     size                       
------------------------------ ------------------------------------------                                                  
TEST_COMPRESS                  3713.54kb

It is because of this reason only that sometimes a 10MB (schema level with rows=n, say) dumpfile created using 10g EXP utility

takes 10GB of space while importing and we wonder why is it taking so much space and keep adding datafile to the tablespace.

So be careful,  always use compress=n when using 10g exp utility.

But, the same is not true with 9i exp utility.Using compress=y or n results the same.Testing compress=y (by default)

matrix >create table test_compress as select * from all_objects;

Table created.

matrix >select count(1) from test_compress;

 COUNT(1)
----------
 27696 

matrix >select table_name,INITIAL_EXTENT,NEXT_EXTENT from user_tables where
table_name='TEST_COMPRESS';

TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT                                                              
------------------------------ -------------- -----------                                                              
TEST_COMPRESS                           65536                                                                          

matrix >select sum(bytes)/1024/1024 "MB" from user_extents where
segment_name='TEST_COMPRESS';

 MB
----------
 4 

matrix >delete test_compress where rownum<10000;

9999 rows deleted.

matrix >commit;

Commit complete.

matrix >select sum(bytes)/1024/1024 "MB" from user_extents where
segment_name='TEST_COMPRESS';

 MB
----------
 4
matrix >REM TOOK EXPORT OF THE TABLE "exp file=test_compress.dmp log=test_compress_10g.log
tables=test_compress feedback=1000"

matrix >REM COMPRESS parameter is ‘Y’ by default in exp
matrix >REM Will drop the table now and import it back
matrix >drop table TEST_COMPRESS;

Table dropped.

matrix >REM imported back the table
matrix >select table_name,INITIAL_EXTENT,NEXT_EXTENT from user_tables where
table_name='TEST_COMPRESS';

TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
TEST_COMPRESS                           65536            
matrix >select sum(bytes)/1024/1024 "MB" from user_extents where
segment_name='TEST_COMPRESS';

 MB
----------
 3

The testing has been done on 9.2.0.6.In 9i the initial extent remains the same as of the original table but in 10g the initial extent takes the size of the segment when using exp/imp utility with default compress=y parameter.

Hope you find in useful, and understand why a structure backup using exp utility  in 10g takes more space while importing it. :)

About these ads

One thought on “EXP – COMPRESS parameter in 9i and 10g

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s