admin管理员组文章数量:1660167
在使用impdp/expdp的时候，使用compress参数，可以是dump文件压缩。目前的测试，压缩率还可以。时间上消耗不是很大（可能和数据量大小有关，暂时测试不出）
没有启用压缩，导出的文件。大小540M
[oracle@test02 ~]$ expdp bb/oracle dumpfile=dump:cc_tbs1.dmp logfile=dump:cc_tbs_exp1.log tablespaces=CC
Export: Release 11.2.0.4.0 - Production on Tue Mar 13 08:48:41 2018
Copyright (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, OLAP, Data Mining and Real Application Testing options
Starting "BB"."SYS_EXPORT_TABLESPACE_01": bb/******** dumpfile=dump:cc_tbs1.dmp logfile=dump:cc_tbs_exp1.log tablespaces=CC
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 631 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "BB"."BB_T1" 540.6 MB 5581504 rows
Master table "BB"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BB.SYS_EXPORT_TABLESPACE_01 is:
/u01/dump/cc_tbs1.dmp
Job "BB"."SYS_EXPORT_TABLESPACE_01" successfully completed at Tue Mar 13 08:48:56 2018 elapsed 0 00:00:14启用压缩。导出数据。数据量70M
[oracle@test02 ~]$ expdp \"/ as sysdba\" dumpfile=dump:cc_tbs2.dmp logfile=dump:cc_tbs_exp2.log tablespaces=DD COMPRESSION=ALL
Export: Release 11.2.0.4.0 - Production on Tue Mar 13 09:35:00 2018
Copyright (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, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLESPACE_01": "/******** AS SYSDBA" dumpfile=dump:cc_tbs2.dmp logfile=dump:cc_tbs_exp2.log tablespaces=DD COMPRESSION=ALL
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 632.2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "BB"."BB_T1" 70.36 MB 5581504 rows
. . exported "CC"."SYS_IMPORT_FULL_01" 17.15 KB 402 rows
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
/u01/dump/cc_tbs2.dmp
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Tue Mar 13 09:35:34 2018 elapsed 0 00:00:33estimate_only参数。评估导出文件的大小。而不会启用导出job作业
[oracle@test02 ~]$ expdp \"/ as sysdba\" tablespaces=DD ESTIMATE_ONLY=Y
Export: Release 11.2.0.4.0 - Production on Tue Mar 13 10:24:08 2018
Copyright (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, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLESPACE_01": "/******** AS SYSDBA" tablespaces=DD ESTIMATE_ONLY=Y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "BB"."BB_T1" 632 MB
. estimated "CC"."SYS_IMPORT_FULL_01" 256 KB
Total estimation using BLOCKS method: 632.2 MB
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Tue Mar 13 10:24:11 2018 elapsed 0 00:00:02
[oracle@test02 ~]$ 但是，使用estimate参数，则是会启动job作业，导出文件的
[oracle@test02 ~]$ expdp \"/ as sysdba\" dumpfile=dump:cc_tbs3.dmp logfile=dump:cc_tbs_exp3.log tablespaces=DD ESTIMATE
Export: Release 11.2.0.4.0 - Production on Tue Mar 13 09:47:27 2018
Copyright (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, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLESPACE_01": "/******** AS SYSDBA" dumpfile=dump:cc_tbs3.dmp logfile=dump:cc_tbs_exp3.log tablespaces=DD ESTIMATE
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 632.2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "BB"."BB_T1" 540.6 MB 5581504 rows
. . exported "CC"."SYS_IMPORT_FULL_01" 87.55 KB 402 rows
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
/u01/dump/cc_tbs3.dmp
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Tue Mar 13 09:47:55 2018 elapsed 0 00:00:27
使用query、exclude参数。需要使用par参数文件。否则会出现一些错误
par的参数文件、导出过程如下：
directory=dump
dumpfile=dump123.dmp
content=data_only
exclude=table:"in('countries','locations','regions')"
query=employees:"where department_id!=20 order by employee_id" [oracle@test02 dump]$ expdp hr/hr parfile=exp.par
Export: Release 11.2.0.4.0 - Production on Tue Mar 13 13:29:21 2018
Copyright (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, OLAP, Data Mining and Real Application Testing options
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/******** parfile=exp.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.60 KB 104 rows
. . exported "HR"."JOBS" 6.992 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."REGIONS" 5.476 KB 4 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
/u01/dump/dump123.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Mar 13 13:29:28 2018 elapsed 0 00:00:07再次测试。导出bb.bb_t1中的部分数据
directory=dump
dumpfile=dump_query.dmp
logfile=dump_query.log
tablespaces=DD
QUERY=BB.BB_T1:"where object_id<10"[oracle@test02 dump]$ expdp \"/ as sysdba\" parfile=expdp_query.par
Export: Release 11.2.0.4.0 - Production on Tue Mar 13 13:34:43 2018
Copyright (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, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLESPACE_01": "/******** AS SYSDBA" parfile=expdp_query.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 632.2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "BB"."BB_T1" 51.14 KB 512 rows
. . exported "CC"."SYS_IMPORT_FULL_01" 87.55 KB 402 rows
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
/u01/dump/dump_query.dmp
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Tue Mar 13 13:35:02 2018 elapsed 0 00:00:19
[oracle@test02 dump]$ SYS@orcl>select count(*) from bb.bb_t1 where object_id<10;
COUNT(*)
----------
512
SYS@orcl>
END
本文标签： 参数impdpEXPDPcompressexclude
版权声明：本文标题：expdpimpdp 的一些参数，compress、estimate、query、exclude 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：https://m.elefans.com/xitong/1729849805a1215262.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论