DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, August 31, 2015

Data Pump Parameter-compression



Data Pump Parameter-compression

Prior to 11g only METADATA_ONLY and NONE could be specified. With now COMPRESSION=ALL, dumpfile size can be greatly reduced so no need to use any compression utility to compress the dumpfile further.



METDATA_ONLY - only the metadata is compressed
DATA_ONLY - only the data is compressed; the metadata is left alone.
ALL - both the metadata and data are compressed.
NONE 
- this is the default; no compression is performed. 


Without compression Parameter
*************************


[oracle@oracle11g dump]$ expdp system/oracle schemas=scott directory=TEST_DIR dumpfile=scott.dmp logfile=scott.log

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 13:45:31 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=scott directory=TEST_DIR dumpfile=scott.dmp logfile=scott.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."RAM"                               5.039 KB       5 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/dump/scott.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:47:52

[oracle@oracle11g dump]$
[oracle@oracle11g dump]$ ls -lrt
total 244
-rw-r--r-- 1 oracle oinstall   1775 Aug 31 13:47 scott.log
-rw-r----- 1 oracle oinstall 241664 Aug 31 13:47 scott.dmp




With compression Parameter
************************


 expdp system/oracle schemas=scott directory=TEST_DIR dumpfile=scott1.dmp logfile=scott1.log compression=all



[oracle@oracle11g dump]$ expdp system/oracle schemas=scott directory=TEST_DIR dumpfile=scott1.dmp logfile=scott1.log compression=all

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 13:48:31 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=scott directory=TEST_DIR dumpfile=scott1.dmp logfile=scott1.log compression=all
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT"                              4.976 KB       4 rows
. . exported "SCOTT"."EMP"                               5.640 KB      14 rows
. . exported "SCOTT"."RAM"                               4.703 KB       5 rows
. . exported "SCOTT"."SALGRADE"                          4.890 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/dump/scott1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:49:05

[oracle@oracle11g dump]$ ls -lrt
total 348
-rw-r----- 1 oracle oinstall 241664 Aug 31 13:47 scott.dmp  Without compressed
-rw-r--r-- 1 oracle oinstall    484 Aug 31 13:48 scott.log
-rw-r--r-- 1 oracle oinstall   1794 Aug 31 13:49 scott1.log
-rw-r----- 1 oracle oinstall  98304 Aug 31 13:49 scott1.dmp   compressed 

No comments: