To see this working, head to your live site.

In Oracle Database how Compress=Y parameter works in Datapump utility.

in Oracle Database Administration

What actually happens in the background when someone uses this Compress=Y parameter in the Export utility?

1 answer0 replies

1 Comment

Piyush Ranjan

Nov 18, 2022

•

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.

When you use COMPRESS=Y to export a table, the INITIAL extent for that table will be the sum of all extents for the table. This can cause problems if you have limited space, as the table will take up more space than is actually needed. If you use COMPRESS=N, the INITIAL extent for the table will be the same as it was in the original database, and new extents will be allocated as needed. This means that the table in the new database will only be as large as it needs to be.

Let's suppose I have a 100 MB table now. There are only 50 MB of real data because of several removals and updates. I rebuild the table in another database after exporting it with COMPRESS=y. While building the table, it will add up all the extents and designate as the INITIAL extent. The table only has 50 MB of data, yet it has already allotted 100 MB. This is not a very excellent alternative if you have a little area.

When importing a table with COMPRESS=N, the table's INITIAL extent will match the INITIAL extension of the source database and further extents will be allocated as needed. My table would now take up about 50 MB of space in the new database.

With the aid of a tabular example, let's do the same.

Let's first construct a table T in the Scott schema and inspect the information from several views:

SQL> create table t as select * from all_objects;
Table created.

SQL> select owner,table_name,INITIAL_EXTENT,NEXT_EXTENT from dba_tables where table_name='T' and owner='SCOTT';

OWNER   TABLE_NAME     INITIAL_EXTENT    NEXT_EXTENT
------- -----------    --------------    ------------
SCOTT	   T			65536	          1048576

SQL> select sum(bytes)/1024/1024 "MB" from dba_extents where segment_name='T' and owner='SCOTT';
	MB
----------
	11


 SQL> select extent_id,bytes/1024 "Kb" from dba_extents where segment_name='T' and owner='SCOTT';

 EXTENT_ID	   Kb
---------- ----------
	 0	   64
	 1	   64
	 2	   64
	 3	   64
	 4	   64
	 5	   64
	 6	   64
	 7	   64
	 8	   64
	 9	   64
	10	   64

 EXTENT_ID	   Kb
---------- ----------
	11	   64
	12	   64
	13	   64
	14	   64
	15	   64
	16	 1024
	17	 1024
	18	 1024
	19	 1024
	20	 1024
	21	 1024

 EXTENT_ID	   Kb
---------- ----------
	22	 1024
	23	 1024
	24	 1024
	25	 1024
 26 rows selected.

I'll now delete a few rows from the table and check DBA SEGMENTS to see how big it is.

SQL> delete t where rownum<31445;
31444 rows deleted.

SQL> commit;
Commit complete.

SQL> select sum(bytes)/1024/1024 "MB" from dba_extents where segment_name='T' and owner='SCOTT';
	MB
----------
	11

The size is still 11 MB as Oracle has not freed the allocated extents.

Case 1: COMPRESS=y

Now in first case we export the table T with compress=y (which is the default), drop the table and import it back. Then we will check the size of the INITIAL extent, size of the table and information from DBA_SEGMENTS.

[oracle@linux u01]$ expdp directory=datapump dumpfile=exp_t.dmp logfile=exp_t.log tables='SCOTT.T';

Export: Release 19.0.0.0.0 - Production on Sat Nov 19 12:22:42 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: scott
Password: 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=datapump dumpfile=exp_t.dmp logfile=exp_t.log tables=SCOTT.T 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T"                                 4.908 MB   35814 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/dp_exp_dir/exp_t.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Nov 19 12:23:30 2022 elapsed 0 00:00:24

SQL> drop table t;
 
Table dropped.
 
[oracle@linux u01]$ impdp directory=datapump dumpfile=exp_t.dmp logfile=imp_t.log tables='T';

Import: Release 19.0.0.0.0 - Production on Sat Nov 19 12:28:03 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: scott
Password: 
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=datapump dumpfile=exp_t.dmp logfile=imp_t.log tables=T 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T"                                 4.908 MB   35814 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Sat Nov 19 12:28:45 2022 elapsed 0 00:00:19

SQL> select owner,table_name,INITIAL_EXTENT,NEXT_EXTENT from dba_tables where table_name='T' and owner='SCOTT';

OWNER	 TABLE_NAME 	INITIAL_EXTENT   NEXT_EXTENT
-------  ----------      --------------  -----------
SCOTT	     T	            65536           1048576

SQL> select extent_id,bytes/1024 "Kb" from dba_extents where segment_name='T' and owner='SCOTT';

 EXTENT_ID	   Kb
---------- ----------
	 0	   64
	 1	   64
	 2	   64
	 3	   64
	 4	   64
	 5	   64
	 6	   64
	 7	   64
	 8	   64
	 9	   64
	10	   64

 EXTENT_ID	   Kb
---------- ----------
	11	   64
	12	   64
	13	   64
	14	   64
	15	   64
	16	 1024
	17	 1024
	18	 1024
	19	 1024
	20	 1024
	
	21 rows selected.

So here we see the INITIAL extent is of 7340032 bytes (7 MB) which is sum of all the extents allocated to the table originally. Lets now see the actual data in the table. Gather stats on the table and calculate the size of total data from avg_row_len.

SQL> exec dbms_stats.gather_table_stats('SCOTT','T');
PL/SQL procedure successfully completed.


SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size" from dba_tables where table_name='T' and owner='SCOTT';

TABLE_NAME	            size
-------------------- --------------------
T		               4826.5kb

SQL>

So the actual data in the table is only 3 MB but its occupying 7 MB. Now lets look at the 2nd case: COMPRESS=n

Case 2: COMPRESS=n

(Note: Here i will recreate the table T from all_objects as when we imported it in last case, it was created with INITIAL extent of 7 MB and it will be useless to experiment on that)

SQL> drop table t;
 
Table dropped.
 
SQL> create table t as select * from dba_objects;
 
Table created.
 
SQL> delete t where rownum<31445;
 
31444 rows deleted.
 
SQL> 
Commit complete.

[oracle@linux u01]$ expdp directory=datapump dumpfile=exp_t.dmp logfile=exp_t.log tables='SCOTT.T' compress=n

Export: Release 19.0.0.0.0 - Production on Sat Nov 19 13:01:19 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: scott
Password: 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "compress=FALSE" Location: Command Line, ignored.
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=datapump dumpfile=exp_t.dmp logfile=exp_t.log tables=SCOTT.T reuse_dumpfiles=true 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T"                                 5.626 MB   41162 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/dp_exp_dir/exp_t.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Nov 19 13:02:23 2022 elapsed 0 00:00:19

SQL> drop table t;
Table dropped.
 
[oracle@linux dp_exp_dir]$ impdp directory=datapump dumpfile=exp_t.dmp logfile=imp_t.log tables='T';

Import: Release 19.0.0.0.0 - Production on Sat Nov 19 13:05:32 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: scott
Password: 
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=datapump dumpfile=exp_t.dmp logfile=imp_t.log tables=T 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T"                                 5.626 MB   41162 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Sat Nov 19 13:06:15 2022 elapsed 0 00:00:20

SQL> select owner,table_name,INITIAL_EXTENT,NEXT_EXTENT from dba_tables where table_name='T' and owner='SCOTT';


OWNER	   TABLE_NAME INITIAL_EXTENT NEXT_EXTENT
--------- ----------- -------------- -----------
SCOTT      T		   65536     1048576
 
SQL> select sum(bytes)/1024/1024 "MB" from dba_extents where segment_name='T' and owner='SCOTT';

	MB
----------
	 7
 
SQL> select extent_id,bytes/1024 "Kb" from dba_extents where segment_name='T' and owner='SCOTT';
 
 EXTENT_ID	   Kb
---------- ----------
	 0	   64
	 1	   64
	 2	   64
	 3	   64
	 4	   64
	 5	   64
	 6	   64
	 7	   64
	 8	   64
	 9	   64
	10	   64

 EXTENT_ID	   Kb
---------- ----------
	11	   64
	12	   64
	13	   64
	14	   64
	15	   64
	16	 1024
	17	 1024
	18	 1024
	19	 1024
	20	 1024
	21	 1024

22 rows selected.

SQL> exec dbms_stats.gather_table_stats('SCOTT','T');
PL/SQL procedure successfully completed.

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size" from dba_tables where table_name='T' and owner='SCOTT';


TABLE_NAME	     size
---------- --------------------------
T		     5547.22kb

Here, the differences are clear. The INITIAL extent's size has not changed, and the amount of space it takes up is exactly equal to the amount of information in the table.

The lesson here is that if you use locally managed tablespaces, you should always export with COMPRESS=n since extent allocation