How to export schema with compression method by using expdp
Posted by Mir Sayeed Hassan on October 9th, 2017
How to export schema with compression method by using expdp
Consider the below example tested in our test env & verified
Create the directory at OS Level to place the backup of the dumpfile
[oracle@testdb backup]$ mkdir db_dump_bkp
[root@testdb backup]# chmod -R 777 db_dump_bkp/
Create the directory at DB Level & assign the directory with OS dir name along with require privileges
SQL> create directory db_dump_bkp as '/backup/db_dump_bkp'; Directory created.
SQL> grant read,write on directory db_dump_bkp to system; Grant succeeded.
SQL> grant exp_full_database to system; Grant succeeded. SQL> exit
Syntax to export the full db dump
[oracle@testdb db_dump_bkp]$ expdp username/password directory= dumpfile= logfile= schemas= compression=all | data_only | metadata_data | none
-
ALL – Both metadata & data are compressed
-
DATA_ONLY – Only data is compressed
-
METADATA_ONLY – Its default, Only metadata is compressed,
-
NONE – No compression
Example:
COMPRESSION=NONE
[oracle@testdbdb_dump_bkp]$ expdp system directory=db_dump_bkp dumpfile=mir_cmp_none.dmp logfile=mir_cmp_none.log schemas=mir compression=none Export: Release 11.2.0.4.0 - Production on Mon Oct 9 15:17:26 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: ******* Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=db_dump_bkp dumpfile=mir_cmp_none.dmp logfile=mir_cmp_none.log schemas=mir compression=none Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128 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/AUDIT_OBJ Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "MIR"."TEST1" 5.085 KB 5 rows . . exported "MIR"."TEST2" 5.070 KB 3 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /backup/db_dump_bkp/mir_cmp_none.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Oct 9 15:17:32 2017 elapsed 0 00:00:04
[oracle@testdbdb_dump_bkp]$ du -h mir_cmp_none.dmp mir_cmp_none.log 204K mir_cmp_none.dmp 4.0K mir_cmp_none.log
COMPORESSION=ALL
[oracle@testdbdb_dump_bkp]$ expdp system directory=db_dump_bkp dumpfile=mir_cmp_all.dmp logfile=mir_cmp_all.log schemas=mir compression=all Export: Release 11.2.0.4.0 - Production on Mon Oct 9 15:10:05 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: ******* Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=db_dump_bkp dumpfile=mir_cmp_all.dmp logfile=mir_cmp_all.log schemas=mir compression=all Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128 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/AUDIT_OBJ Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "MIR"."TEST1" 4.718 KB 5 rows . . exported "MIR"."TEST2" 4.703 KB 3 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /backup/db_dump_bkp/mir_cmp_all.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Oct 9 15:10:13 2017 elapsed 0 00:00:04
[oracle@testdbdb_dump_bkp]$ du -h mir_cmp_all.dmp mir_cmp_all.log 72K mir_cmp_all.dmp 4.0K mir_cmp_all.log
COMPORESSION=DATA_ONLY
[oracle@testdbdb_dump_bkp]$ expdp system directory=db_dump_bkp dumpfile=mir_cmp_data_only.dmp logfile=mir_cmp_data_only.log schemas=mir compression=data_only Export: Release 11.2.0.4.0 - Production on Mon Oct 9 15:15:59 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=db_dump_bkp dumpfile=mir_cmp_data_only.dmp logfile=mir_cmp_data_only.log schemas=mir compression=data_only Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128 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/AUDIT_OBJ Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "MIR"."TEST1" 4.718 KB 5 rows . . exported "MIR"."TEST2" 4.703 KB 3 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded **************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /backup/db_dump_bkp/mir_cmp_data_only.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Oct 9 15:16:05 2017 elapsed 0 00:00:04
[oracle@testdbdb_dump_bkp]$ du -h mir_cmp_data_only.dmp mir_cmp_data_only.log 76K mir_cmp_data_only.dmp 4.0K mir_cmp_data_only.log
COMPRESSION=METADATA_ONLY
[oracle@testdbdb_dump_bkp]$ expdp system directory=db_dump_bkp dumpfile=mir_cmp_metadata_only.dmp logfile=mir_cmp_metadata_only.log schemas=mir compression=metadata_only Export: Release 11.2.0.4.0 - Production on Mon Oct 9 15:20:10 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password:******* Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=db_dump_bkp dumpfile=mir_cmp_metadata_only.dmp logfile=mir_cmp_metadata_only.log schemas=mir compression=metadata_only Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128 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/AUDIT_OBJ Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "MIR"."TEST1" 5.085 KB 5 rows . . exported "MIR"."TEST2" 5.070 KB 3 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /backup/db_dump_bkp/mir_cmp_metadata_only.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Oct 9 5:20:17 2017 elapsed 0 00:00:04
[oracle@testdbdb_dump_bkp]$ du -h mir_cmp_metadata_only.dmp mir_cmp_metadata_only.log 200K mir_cmp_metadata_only.dmp 4.0K mir_cmp_metadata_only.log
Find the below details of all the above compression method with size difference
Note:
The above sample is tested with the simple schema size; Best practice to use this compression method is more huge data to compress
Let’s compare the size;
[oracle@testdbdb_dump_bkp]$ ls –lrth total 160M -rw-rw----. 1 oracle oracle 204K Oct 9 15:17 mir_cmp_none.dmp -rw-rw----. 1 oracle oracle 72K Oct 9 15:10 mir_cmp_all.dmp -rw-rw----. 1 oracle oracle 76K Oct 9 15:16 mir_cmp_data_only.dmp -rw-rw----. 1 oracle oracle 200K Oct 9 15:20 mir_cmp_metadata_only.dmp