行业知识
Oracle数据泵expdp和impdp导入导出详解
���录
（）一、准备工作
二、数据导出（expdp）
（）三、数据导入（impdp）
Oracle数据泵Expdp/impdp导入导出详解
一、准备工作
- 创建directory 目录，用户导出的数据存放于此目录
SQL> create directory EXPDP_DUMP as '/test/expdp_dump';
2、查看创建的directory
SQL> select * from dba_directories;
3、授予EXPDP_DUMP目录权限给导出用户scott
SQL> grant read, write on directory EXPDP_DUMP to test;
4、删除 EXPDP_DUMP
SQL> drop directory EXPDP_DUMP;
二、数据导出（expdp）
- 参数ATTACH：连接到现有作业。
如：连接已经停止的作业继续备份
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp full=y Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp full=y Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 15.77 GB Processing object type DATABASE_EXPORT/TABLESPACE Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type DATABASE_EXPORT/SCHEMA/USER Processing object type DATABASE_EXPORT/ROLE ^C Export> 备份中断，查看数据库备份状态： SQL> select * from dba_datapump_jobs; OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS -------------------- -------------------- -------------------- ---------- -------------------- ---------- ----------------- ----------------- SYS SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0 0 0 使用attach继续备份 [oracle@neb /home/oracle]$ expdp \"/ as sysdba \" attach=SYS_EXPORT_FULL_01 Export: Release 11.2.0.3.0 - Production on Thu Dec 3 16:46:10 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Job: SYS_EXPORT_FULL_01 Owner: SYS Operation: EXPORT Creator Privs: TRUE GUID: 25FB5AC28D4955BBE05400144FAD500E Start Time: Thursday, 03 December, 2015 16:46:11 Mode: FULL Instance: neb Max Parallelism: 1 EXPORT Job Parameters: Parameter Name Parameter Value: CLIENT_COMMAND "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp full=y State: IDLING Bytes Processed: 0 Current Parallelism: 1 Job Error Count: 0 Dump File: /test/expdp_dump/testexp01.dmp bytes written: 4,096 Dump File: /test/expdp_dump/testexp%u.dmp
Worker 1 Status: Process Name: DW00 State: UNDEFINED Export> continue_client Job SYS_EXPORT_FULL_01 has been reopened at Thursday, 03 December, 2015 16:46 Restarting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp full=y Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE . . exported "ZCYH"."VIP_MO" 0 KB 0 rows . . exported "ZCYH"."VIP_MYPAX_BAK" 0 KB 0 rows . . exported "ZCYH"."VIP_MYPAX_TEMP" 0 KB 0 rows . . exported "ZCYH"."VIP_PHOENIX_BINDING" 0 KB 0 rows . . exported "ZCYH"."VIP_PHONES_UPDATE_LOG" 0 KB 0 rows Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_FULL_01 is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 16:21:53 |
2、参数CLUSTER：利用集群资源并将 worker 进程分布在 Oracle RAC 上。
有效的关键字值为: [Y] 和 N。
如：
| 备份时worker 进程分布在 Oracle RAC 的多个实例上默认cluster=y，所以DIRECTORY必须在共享磁盘上，如果没有设置共享磁盘还是指定cluster=no来防止报错。 [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott cluster=n parallel=4 Export: Release 11.2.0.3.0 - Production on Thu Dec 3 18:02:36 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott cluster=n parallel=4 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.125 MB . . exported "SCOTT"."T1" 2.097 MB 22866 rows . . exported "SCOTT"."T" 5.406 KB 1 rows . . exported "SCOTT"."TEST" 5.414 KB 1 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/testexp01.dmp /test/expdp_dump/testexp02.dmp /test/expdp_dump/testexp03.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:02:48 |
3、参数COMPRESSION：关键字值为: ALL（全部压缩）, DATA_ONLY（表数据压缩）
如：
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott cluster=n COMPRESSION=all Export: Release 11.2.0.3.0 - Production on Thu Dec 3 18:17:01 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott cluster=n COMPRESSION=all Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.125 MB 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/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."T1" 270.6 KB 22866 rows . . exported "SCOTT"."T" 4.765 KB 1 rows . . exported "SCOTT"."TEST" 4.773 KB 1 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:17:12 压缩备份大小： [oracle@neb /test/expdp_dump]$ du -sh testexp01.dmp 376K testexp01.dmp 未压缩备份大小： [oracle@neb /test/expdp_dump]$ du -sh testexp01_old.dmp 2.3M testexp01_old.dmp |
4、参数CONTENT： 有效的关键字为: [ALL], DATA_ONLY（只导出数据） 和 METADATA_ONLY（只导出结构）。
如：
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott cluster=n CONTENT=METADATA_ONLY Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:01:59 [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott cluster=n CONTENT=data_only Export: Release 11.2.0.3.0 - Production on Wed Dec 16 16:36:03 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott cluster=n CONTENT=data_only Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.125 MB . . exported "SCOTT"."T1" 2.097 MB 22862 rows . . exported "SCOTT"."T" 5.406 KB 1 rows . . exported "SCOTT"."TEST" 5.414 KB 1 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:36:19 |
5、参数encryption_password：导出加密，导入时需要提供密码才能导入
如：
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott encryption_password=abc [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott encryption_password=abc Export: Release 11.2.0.3.0 - Production on Wed Dec 16 16:55:13 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott encryption_password=******** Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.125 MB 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/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."T1" 2.097 MB 22862 rows . . exported "SCOTT"."T" 5.414 KB 1 rows . . exported "SCOTT"."TEST" 5.421 KB 1 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:55:29 导入时需要提供密码 [oracle@neb /test/expdp_dump]$ impdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp REMAP_SCHEMA=scott:test encryption_password=abc Import: Release 11.2.0.3.0 - Production on Wed Dec 16 16:58:42 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp REMAP_SCHEMA=scott:test encryption_password=******** Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"TEST" already exists 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/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "TEST"."T1" 2.097 MB 22862 rows . . imported "TEST"."T" 5.414 KB 1 rows . . imported "TEST"."TEST" 5.421 KB 1 rows Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 16:58:48 |
6、参数ESTIMATE={BLOCKS | STATISTICS}，设置为BLOCKS时,oracle会按照目标对象所占用的数据块个数乘以数据块尺寸估算对象占用的空间,设置为STATISTICS时,根据最近统计值估算对象占用空间
如：
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott ESTIMATE=BLOCKS Export: Release 11.2.0.3.0 - Production on Tue Dec 22 17:35:48 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott ESTIMATE=BLOCKS Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . estimated "SCOTT"."T1" 3 MB . estimated "SCOTT"."T" 64 KB . estimated "SCOTT"."TEST" 64 KB Total estimation using BLOCKS method: 3.125 MB 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/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."T1" 2.097 MB 22862 rows . . exported "SCOTT"."T" 5.406 KB 1 rows . . exported "SCOTT"."TEST" 5.414 KB 1 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:36:49 |
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott ESTIMATE=STATISTICS Export: Release 11.2.0.3.0 - Production on Tue Dec 22 17:46:00 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott ESTIMATE=STATISTICS Estimate in progress using STATISTICS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . estimated "SCOTT"."T1" 2.037 MB . estimated "SCOTT"."T" 5.438 KB . estimated "SCOTT"."TEST" 5.438 KB Total estimation using STATISTICS method: 2.048 MB 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/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."T1" 2.097 MB 22862 rows . . exported "SCOTT"."T" 5.406 KB 1 rows . . exported "SCOTT"."TEST" 5.414 KB 1 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:46:17 |
7、参数ESTIMATE_ONLY={Y|N} 指定是否只估算导出作业所占用的磁盘空间,默认值为N，设置为Y时,导出作用只估算对象所占用的磁盘空间,而不会执行导出作业,为N时,不仅估算对象所占用的磁盘空间,还会执行导出操作
如：
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" schemas=scott ESTIMATE_ONLY=y Export: Release 11.2.0.3.0 - Production on Wed Dec 23 10:10:41 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" schemas=scott ESTIMATE_ONLY=y Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . estimated "SCOTT"."T1" 3 MB . estimated "SCOTT"."T" 64 KB . estimated "SCOTT"."TEST" 64 KB Total estimation using BLOCKS method: 3.125 MB Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:10:47 |
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott ESTIMATE_ONLY=N Export: Release 11.2.0.3.0 - Production on Wed Dec 23 10:11:27 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp schemas=scott ESTIMATE_ONLY=N Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.125 MB 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/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."T1" 2.097 MB 22862 rows . . exported "SCOTT"."T" 5.406 KB 1 rows . . exported "SCOTT"."TEST" 5.414 KB 1 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:11:38 |
8、参数EXCLUDE=object_type[:name_clause]?[,….]，与参数INCLUDE不能同时使用
如：
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp EXCLUDE=SCHEMA:\"IN \(\'WMSYS\', \'OUTLN\', \'APEX_030200\',\'SYS\',\'SYSTEM\',\'OLAPSYS\',\'SYSMAN\',\'ORDDATA\'\)\" FULL=y Export: Release 11.2.0.3.0 - Production on Wed Dec 23 10:14:43 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_FULL_02": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp EXCLUDE=SCHEMA:"IN ('WMSYS', 'OUTLN','APEX_030200','SYS','SYSTEM','OLAPSYS','SYSMAN','ORDDATA')" FULL=y Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 15.66 GB Processing object type DATABASE_EXPORT/TABLESPACE Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type DATABASE_EXPORT/SCHEMA/USER Processing object type DATABASE_EXPORT/ROLE Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA Processing object type DATABASE_EXPORT/RESOURCE_COST Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/CONTEXT Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW Processing object type DATABASE_EXPORT/SCHEMA/JOB Processing object type DATABASE_EXPORT/SCHEMA/REFRESH_GROUP Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/SCHEMA/PASSWORD_HISTORY Processing object type DATABASE_EXPORT/AUDIT . . exported "AIRFARE"."RECORD_LAUNCH" 1.994 GB 71315562 rows . . exported "ZCYH"."ORDER_PNR" 1.951 GB 5584445 rows . . exported "ZCYH"."VIP_MYPAX_BAK" 0 KB 0 rows . . exported "ZCYH"."VIP_MYPAX_TEMP" 0 KB 0 rows . . exported "ZCYH"."VIP_PHOENIX_BINDING" 0 KB 0 rows . . exported "ZCYH"."VIP_PHONES_UPDATE_LOG" 0 KB 0 rows Master table "SYS"."SYS_EXPORT_FULL_02" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_FULL_02 is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_EXPORT_FULL_02" successfully completed at 10:29:42 |
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=test%u.dmp SCHEMAS=scott EXCLUDE=TABLE:\"IN\(\'T1\'\)\" Export: Release 11.2.0.3.0 - Production on Wed Dec 23 10:31:53 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=test%u.dmp SCHEMAS=scott EXCLUDE=TABLE:"IN('T1')" 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/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."T" 5.406 KB 1 rows . . exported "SCOTT"."TEST" 5.414 KB 1 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/test01.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:32:32 |
9、参数FILESIZE：以字节为单位指定每个转储文件的大小。
如：
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp SCHEMAS=AIRFARE FILESIZE=2G Export: Release 11.2.0.3.0 - Production on Wed Dec 23 10:38:21 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp SCHEMAS=AIRFARE FILESIZE=2G Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 6.892 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/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT 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/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT 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/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA . . exported "AIRFARE"."RECORD_LAUNCH" 1.994 GB 71315562 rows . . exported "AIRFARE"."SHEB_STATICP_MIDDLE_ZZ_HIS_B2C" 1.547 GB 19328985 rows . . exported "AIRFARE"."SHEB_STATICPCALCULDC_HISTORY" 360.0 MB 5976468 rows . . exported "AIRFARE"."SHEB_AIRFARE" 291.1 MB 810214 rows . . exported "AIRFARE"."FARE_FAREMAIN" 154.9 MB 477225 rows . . exported "AIRFARE"."SHEB_STATICP_MIDDLE_WF_B2C" 171.2 MB 2494916 rows . . exported "AIRFARE"."SHEB_STATICP_MIDDLE_WF" 171.2 MB 2494871 rows . . exported "AIRFARE"."SHEB_STATICP_05_ZZ_B2C" 170.8 MB 2083789 rows . . exported "AIRFARE"."SHEB_STATICP_04_ZZ_B2C" 92.53 MB 1128247 rows . . exported "AIRFARE"."SHEB_STATICP_MIDDLE_DC" 119.2 MB 1887542 rows . . exported "AIRFARE"."SHEB_STATICP_MIDDLE_DC_B2C" 125.4 MB 1992193 rows . . exported "AIRFARE"."OS_PROPERTYSET" 78.23 MB 1756366 rows . . exported "AIRFARE"."SHEB_STATICP_MIDDLE_ZZ_B2C" 82.88 MB 1010517 rows . . exported "AIRFARE"."SHEB_STATICP_TEMP" 0 KB 0 rows . . exported "AIRFARE"."SYS_EXAMPLE" 0 KB 0 rows . . exported "AIRFARE"."SYS_USERSETSON" 0 KB 0 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/testexp01.dmp /test/expdp_dump/testexp02.dmp /test/expdp_dump/testexp03.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:46:36 [oracle@neb /test/expdp_dump]$ du -sh testexp01.dmp 2.0G testexp01.dmp [oracle@neb /test/expdp_dump]$ du -sh testexp02.dmp 2.0G testexp02.dmp [oracle@neb /test/expdp_dump]$ du -sh testexp03.dmp 1.7G testexp03.dmp [oracle@neb /test/expdp_dump]$ |
10、参数FLASHBACK_SCN用于重置会话快照的 SCN，与参数FLASHBACK_TIME不能同时使用
如：
| 11:06:51 sys@neb(airflite-sm-db)> select count(*) from TEST; COUNT(*) ---------- 1 Elapsed: 00:00:00.00 11:07:00 sys@neb(airflite-sm-db)> select * from test; ID NAME ---------- ------------------------------------------------------------ 1 q Elapsed: 00:00:00.01 11:07:07 sys@neb(airflite-sm-db)> select current_scn from v$database; CURRENT_SCN ---------------- 10297641570774 Elapsed: 00:00:00.00 11:07:19 sys@neb(airflite-sm-db)> insert into test values(2,'w'); 1 row created. Elapsed: 00:00:00.00 11:07:43 sys@neb(airflite-sm-db)> commit; Commit complete. Elapsed: 00:00:00.00 11:07:46 sys@neb(airflite-sm-db)> select current_scn from v$database; CURRENT_SCN ---------------- 10297641570814 [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp SCHEMAS=scott flashback_scn=10297641570774 Export: Release 11.2.0.3.0 - Production on Wed Dec 23 11:09:00 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp SCHEMAS=scott flashback_scn=10297641570774 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.125 MB 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/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."T1" 2.097 MB 22862 rows . . exported "SCOTT"."T" 5.406 KB 1 rows . . exported "SCOTT"."TEST" 5.414 KB 1 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:09:11 [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp SCHEMAS=scott flashback_scn=10297641570814 Export: Release 11.2.0.3.0 - Production on Wed Dec 23 11:10:06 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp SCHEMAS=scott flashback_scn=10297641570814 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.125 MB 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/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."T1" 2.097 MB 22862 rows . . exported "SCOTT"."T" 5.406 KB 1 rows . . exported "SCOTT"."TEST" 5.421 KB 2 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:10:17 |
11、参数FLASHBACK_TIME：用于查找最接近的相应 SCN 值的时间
如：
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp SCHEMAS=scott flashback_time="to_timestamp(localtimestamp)" Export: Release 11.2.0.3.0 - Production on Wed Dec 23 11:29:02 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp SCHEMAS=scott flashback_time=to_timestamp (localtimestamp) Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.125 MB 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/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."T1" 2.097 MB 22862 rows . . exported "SCOTT"."T" 5.406 KB 1 rows . . exported "SCOTT"."TEST" 5.414 KB 1 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:29:15 |
12、参数FULL：导出全库
如：
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp full=y Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp full=y Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 15.77 GB Processing object type DATABASE_EXPORT/TABLESPACE Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type DATABASE_EXPORT/SCHEMA/USER Processing object type DATABASE_EXPORT/ROLE . . exported "AIRFARE"."RECORD_LAUNCH" 1.994 GB 71315562 rows . . exported "ZCYH"."ORDER_PNR" 1.951 GB 5584445 rows . . exported "ZCYH"."VIP_MYPAX_BAK" 0 KB 0 rows . . exported "ZCYH"."VIP_MYPAX_TEMP" 0 KB 0 rows . . exported "ZCYH"."VIP_PHOENIX_BINDING" 0 KB 0 rows . . exported "ZCYH"."VIP_PHONES_UPDATE_LOG" 0 KB 0 rows Master table "SYS"."SYS_EXPORT_FULL_02" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_FULL_02 is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_EXPORT_FULL_02" successfully completed at 10:29:42 |
13、参数INCLUDE = object_type[:name_clause] [, ...] INCLUDE选项和EXCLUDE选项不能同时使用
如：
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp INCLUDE=SCHEMA:\"IN \(\'SCOTT\'\)\" FULL=y Export: Release 11.2.0.3.0 - Production on Wed Dec 23 11:35:09 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_FULL_02": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp INCLUDE=SCHEMA:"IN ('SCOTT')" FULL=y Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.125 MB Processing object type DATABASE_EXPORT/SCHEMA/USER Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/PASSWORD_HISTORY . . exported "SCOTT"."T1" 2.097 MB 22862 rows . . exported "SCOTT"."T" 5.406 KB 1 rows . . exported "SCOTT"."TEST" 5.429 KB 3 rows Master table "SYS"."SYS_EXPORT_FULL_02" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_FULL_02 is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_EXPORT_FULL_02" successfully completed at 11:36:04 |
14、参数JOB_NAME：要创建的导出作业的名称。默认为SYS_XXX
如：
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp JOB_NAME=SYS_SCOTT SCHEMAS=SCOTT Export: Release 11.2.0.3.0 - Production on Wed Dec 23 11:39:06 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_SCOTT": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp JOB_NAME=SYS_SCOTT SCHEMAS=SCOTT Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.125 MB 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/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."T1" 2.097 MB 22862 rows . . exported "SCOTT"."T" 5.406 KB 1 rows . . exported "SCOTT"."TEST" 5.429 KB 3 rows Master table "SYS"."SYS_SCOTT" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_SCOTT is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_SCOTT" successfully completed at 11:39:28 |
15、参数NETWORK_LINK：源系统的远程数据库链接的名称。客户端可导出服务端的数据
如：
| 13:44:20 sys@BACKUP> alter user abc identified by abc_test123; User altered. 13:49:31 sys@backup(backup-test)> create directory expdp_pump as '/oradata/test'; Directory created. Elapsed: 00:00:00.13 13:52:26 sys@backup(backup-test)> grant read,write on directory expdp_pump to abc; Elapsed: 00:00:00.16 13:54:34 sys@BACKUP> grant read,write on directory expdp_pump to abc; Grant succeeded. Elapsed: 00:00:00.13 13:54:55 sys@BACKUP> grant connect,resource,create database link to abc; Grant succeeded. Elapsed: 00:00:00.11 13:56:59 abc@BACKUP> create database link testlink connect to scott identified by test_123 using 'neb_test'; Database link created. 13:57:55 sys@BACKUP> quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@backup /oradata/test]$ [oracle@backup /oradata/test]$ [oracle@backup /oradata/test]$ expdp abc/abc_test123 DIRECTORY=EXPDP_PUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp network_link=testlink Export: Release 11.2.0.4.0 - Production on Wed Dec 23 14:02:02 2015 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 "ABC"."SYS_EXPORT_SCHEMA_01": abc/******** DIRECTORY=EXPDP_PUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp network_link=testlink Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.125 MB Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."T1" 2.097 MB 22862 rows . . exported "SCOTT"."T" 5.406 KB 1 rows . . exported "SCOTT"."TEST" 5.429 KB 3 rows Master table "ABC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for ABC.SYS_EXPORT_SCHEMA_01 is: /oradata/test/testexp01.dmp Job "ABC"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Dec 23 14:02:55 2015 elapsed 0 00:00:49 [oracle@backup /oradata/test]$ ls -lrt total 2364 -rw-r--r-- 1 oracle oinstall 1564 Dec 23 14:02 test.log -rw-r----- 1 oracle oinstall 2416640 Dec 23 14:02 testexp01.dmp [oracle@backup /oradata/test]$ |
16、参数NOLOGFILE：不写入日志文件
如：
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp SCHEMAS=scott NOLOGFILE=y Export: Release 11.2.0.3.0 - Production on Wed Dec 23 14:12:46 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp SCHEMAS=scott NOLOGFILE=y Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.125 MB 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/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."T1" 2.097 MB 22862 rows . . exported "SCOTT"."T" 5.406 KB 1 rows . . exported "SCOTT"."TEST" 5.429 KB 3 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/testexp01.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:13:09 [oracle@neb /test/expdp_dump]$ ls -lrt 总数 1252490 -rw-r--r-- 1 oracle oinstall 516743168 9月 9日 16:36 brs.dmp -rw-r--r-- 1 oracle oinstall 110713 9月 9日 16:36 brs.log -rw-r----- 1 oracle oinstall 2437120 12月 23日 14:13 testexp01.dmp [oracle@neb /test/expdp_dump]$ |
17、参数PARALLEL：指定执行导出操作的并行进程个数,默认值为1
如：
| [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp SCHEMAS=scott PARALLEL=4 Export: Release 11.2.0.3.0 - Production on Wed Dec 23 14:15:19 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp SCHEMAS=scott PARALLEL=4 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.125 MB . . exported "SCOTT"."T1" 2.097 MB 22862 rows . . exported "SCOTT"."T" 5.406 KB 1 rows . . exported "SCOTT"."TEST" 5.429 KB 3 rows 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/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/testexp01.dmp /test/expdp_dump/testexp02.dmp /test/expdp_dump/testexp03.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:15:31 |
18、参数 PARFILE：指定参数文件名
如：
| [oracle@neb /test/expdp_dump]$ cat scott_expdp.par DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp SCHEMAS=scott PARALLEL=4 [oracle@neb /test/expdp_dump]$ expdp \"/ as sysdba \" parfile=scott_expdp.par Export: Release 11.2.0.3.0 - Production on Wed Dec 23 14:20:06 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" parfile=scott_expdp.par Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.125 MB . . exported "SCOTT"."T1" 2.097 MB 22862 rows . . exported "SCOTT"."T" 5.406 KB 1 rows . . exported "SCOTT"."TEST" 5.429 KB 3 rows 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/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /test/expdp_dump/testexp01.dmp /test/expdp_dump/testexp02.dmp /test/expdp_dump/testexp03.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:20:17 |
19、参数QUERY： 用于导出表的子集的谓词子句
如：
| SQL> select * from scott.test; ID NAME ---------- -------------------- 1 q 2 w 3 e 4 r 5 t 6 y 7 u 8 i [oracle@neb /test/expdp_dump]$ cat scott_expdp.par DIRECTORY=EXPDP_DUMP LOGFILE=test.log DUMPFILE=testexp%u.dmp Tables=test query="where id 10"。
|
上一篇：网页设计就业怎么样?
下一篇：怎么用服务器进行仿真?
