Expdp with compression option in oracle

by Posted on

It’s absolutely that adopting compression option can reduce occupation of disk space. For example the size of original dmp file which without compression option is 347GB, but if you adopt option “compression=all” ,the size dmp file can be reduced to 91GB. I have performed this operation, believe me. The compression rate will reach to 73%

expdp command(without compression option):

expdp user1/ directory=path dumpfile=export_all_data.201911171600.dmp logfile=export_all_data.201911171600.log

Export: Release 11.2.0.4.0 – Production on Sun Nov 17 16:00:55 2019Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting “user1″.”SYS_EXPORT_SCHEMA_X”: user1/ directory=path dumpfile=export_all_data.201911171600.dmp logfile=export_all_data.201911171600.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320.0 GB
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/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/JOB
. . exported “Tablespace”.”a” 33.03 GB 881268 rows
. . exported “Tablespace”.”b” 8.424 GB 37185291 rows
. . exported “Tablespace”.”c” 5.482 GB 1158561 rows
. . exported “Tablespace”.”d” 5.180 GB 34743472 rows
. . exported “Tablespace”.”e” 4.427 GB 854974 rows
. . exported “Tablespace”.”f” 2.993 GB 3928392 rows
. . exported “Tablespace”.”g” 2.789 GB 19899254 rows
. . exported “Tablespace”.”h” 2.717 GB 97161 rows
. . exported “Tablespace”.”i” 2.445 GB 7535888 rows
. . exported “Tablespace”.”j” 2.322 GB 17479512 rows
. . exported “Tablespace”.”k” 1.773 GB 7788784 rows
……………………….
……………………….
. . exported “Table”.”x” 0 KB 0 rows
. . exported “Table”.”y” 0 KB 0 rows
. . exported “Table”.”z” 0 KB 0 rows
Master table “Table”.”SYS_EXPORT_SCHEMA_02″ successfully loaded/unloadedDump file set for Table.SYS_EXPORT_SCHEMA_02 is:
/path/…/path/export_all_data.201911171600.dmp
Job “Tablespace”.”SYS_EXPORT_SCHEMA_02″ successfully completed at Mon Nov 18 05:04:04 2019 elapsed 0 13:00:00

expdp command (with compression option)

expdp user1/ directory=path dumpfile=export_all_data.201911201600.dmp logfile=export_all_data.201911201600.log compression=all

Export: Release 11.2.0.4.0 – Production on Sun Nov 17 16:00:55 2019Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
Starting “user1″.”SYS_EXPORT_SCHEMA_X”: user1/ directory=path dumpfile=export_all_data.201911201600.dmp logfile=export_all_data.201911201600.log compression=all
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320.0 GB
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/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/JOB
. . exported “Tablespace”.”a” 33.03 GB 881268 rows
. . exported “Tablespace”.”b” 8.424 GB 37185291 rows
. . exported “Tablespace”.”c” 5.482 GB 1158561 rows
. . exported “Tablespace”.”d” 5.180 GB 34743472 rows
. . exported “Tablespace”.”e” 4.427 GB 854974 rows
. . exported “Tablespace”.”f” 2.993 GB 3928392 rows
. . exported “Tablespace”.”g” 2.789 GB 19899254 rows
. . exported “Tablespace”.”h” 2.717 GB 97161 rows
. . exported “Tablespace”.”i” 2.445 GB 7535888 rows
. . exported “Tablespace”.”j” 2.322 GB 17479512 rows
. . exported “Tablespace”.”k” 1.773 GB 7788784 rows
……………………….
……………………….
. . exported “Table”.”x” 0 KB 0 rows
. . exported “Table”.”y” 0 KB 0 rows
. . exported “Table”.”z” 0 KB 0 rows
Master table “Table”.”SYS_EXPORT_SCHEMA_02″ successfully loaded/unloaded
Dump file set for Table.SYS_EXPORT_SCHEMA_02 is:
/path/…/path/export_all_data.201911201600.dmp
Job “Tablespace”.”SYS_EXPORT_SCHEMA_02″ successfully completed at Mon Nov 21 08:26:04 2019 elapsed 0 16:21:00

But you must be aware of option “compression=all” will delay exporting time. According to the above export process, It’s obviously that time is 13 hours without option “compression=all”. But the time will reach to 16.5 hours with option “compression=all”.

By the way, If you import dmp file which is with option “compression=all” to an oracle database, the time cost will be longer than importing dmp file which is without option “compression=all”.

Other compression option as shown in following.

compression=DATA_ONLY
compression=METADATA_ONLY
compression=DEFAULT

Donate

Published by byron

Leave a Reply