使用EXPDP逻辑备份工具的EXCLUDE选项可以指定那类数据库对象不被导出，EXPDP工具的前身EXP如果想要完成同样的任务非常的困难。我们以排除部分表为例看一下这个选项带给我们的便利。
如果在命令行中完成备份，特殊字符的转义需要特别注意（我这里使用的是Linux操作系统）。使用EXPDP逻辑备份工具的EXCLUDE选项可以指定那类数据库对象不被导出，EXPDP工具的前身EXP如果想要完成同样的任务非常的困难。我们以排除部分表为例看一下这个选项带给我们的便利。
如果在命令行中完成备份，特殊字符的转义需要特别注意（我这里使用的是Linux操作系统）。
1. 帮助信息：
[oracle@dba ~]$ expdp -help
Export: Release 11.2.0.4.0 - Production on Thu Feb 12 09:42:06 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:
Format: expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
------------------------------------------------------------------------------
The available keywords and their descriptions follow. Default values are listed within square brackets.
2. 建立相应的目录，授予权限：
SQL> create or replace directory expdir as '/home/oracle/expdir';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY EXPDIR TO SCOTT;
Grant succeeded.
SQL> GRANT READ,WRITE ON DIRECTORY EXPDIR TO fei;
Grant succeeded.
3. 操作系统信息：
[oracle@dba ~]$ uname -a
Linux dba.fei.com 2.6.32-358.el6.x86_64 #1 SMP Tue Jan 29 11:47:41 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
[oracle@dba expdir]$ expdp fei/fei directory=expdir dumpfile=fei2.dmp logfile=fei.log
Export: Release 11.2.0.4.0 - Production on Thu Feb 12 10:22: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.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "FEI"."SYS_EXPORT_SCHEMA_03": fei/******** directory=expdir dumpfile=fei2.dmp logfile=fei.log
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS []
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 9710
----- PL/SQL Call Stack -----
object line object
handle number name
0x886d0090 21979 package body SYS.KUPW$WORKER
0x886d0090 9742 package body SYS.KUPW$WORKER
0x886d0090 10573 package body SYS.KUPW$WORKER
0x886d0090 1824 package body SYS.KUPW$WORKER
0x8844ae58 2 anonymous block
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS []
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 9710
----- PL/SQL Call Stack -----
object line object
handle number name
0x886d0090 21979 package body SYS.KUPW$WORKER
0x886d0090 9742 package body SYS.KUPW$WORKER
0x886d0090 10573 package body SYS.KUPW$WORKER
0x886d0090 1824 package body SYS.KUPW$WORKER
0x8844ae58 2 anonymous block
Job "FEI"."SYS_EXPORT_SCHEMA_03" stopped due to fatal error at Thu Feb 12 10:22:56 2015 elapsed 0 00:00:02
[oracle@dba expdir]$ expdp fei/fei directory=expdir dumpfile=fei2.dmp logfile=fei.log
Export: Release 11.2.0.4.0 - Production on Thu Feb 12 10:42:47 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
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/home/oracle/expdir/fei2.dmp"
ORA-27038: created file already exists
Additional information: 1
SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------
SQL> select * from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- -------------
/u01/app/oracle/oradata/lote/example01.dbf 5 EXAMPLE 328335360 40080 AVAILABLE 5 YES 3435972198 4194302 80 327286784 39952 ONLINE
/u01/app/oracle/oradata/lote/users01.dbf 4 USERS 73400320 8960 AVAILABLE 4 YES 3435972198 4194302 160 72351744 8832 ONLINE
/u01/app/oracle/oradata/lote/undotbs01.dbf 3 UNDOTBS1 110100480 13440 AVAILABLE 3 YES 3435972198 4194302 640 109051904 13312 ONLINE
/u01/app/oracle/oradata/lote/sysaux01.dbf 2 SYSAUX 576716800 70400 AVAILABLE 2 YES 3435972198 4194302 1280 575668224 70272 ONLINE
/u01/app/oracle/oradata/lote/system01.dbf 1 SYSTEM 786432000 96000 AVAILABLE 1 YES 3435972198 4194302 1280 785383424 95872 SYSTEM
/u01/app/oracle/oradata/lote/lofe01.dbf 6 LOFE 52428800 6400 AVAILABLE 6 NO 0 0 0 51380224 6272 ONLINE
/u01/app/oracle/oradata/lote/lofe02.dbf 7 LOFE 209715200 25600 AVAILABLE 7 NO 0 0 0 208666624 25472 ONLINE
7 rows selected
SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------
1 1227158 2015/2/12 10: 3 1 ONLINE READ WRITE 2097152000 256000 2097152000 8192 /u01/app/oracle/oradata/lote/temp01.dbf
[oracle@dba expdir]$ expdp fei/fei directory=expdir dumpfile=fei3.dmp logfile=fei.log
Export: Release 11.2.0.4.0 - Production on Thu Feb 12 10:42:54 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 "FEI"."SYS_EXPORT_SCHEMA_04": fei/******** directory=expdir dumpfile=fei3.dmp logfile=fei.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 960 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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
. . exported "FEI"."SYS_EXPORT_SCHEMA_01" 137.8 KB 1166 rows
. . exported "FEI"."SYS_EXPORT_SCHEMA_02" 137.8 KB 1166 rows
. . exported "FEI"."SYS_EXPORT_SCHEMA_03" 137.8 KB 1166 rows
. . exported "FEI"."T1" 5.015 KB 1 rows
. . exported "FEI"."T2" 5.015 KB 1 rows
. . exported "FEI"."T3" 5.015 KB 1 rows
Master table "FEI"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for FEI.SYS_EXPORT_SCHEMA_04 is:
/home/oracle/expdir/fei3.dmp
Job "FEI"."SYS_EXPORT_SCHEMA_04" successfully completed at Thu Feb 12 10:43:39 2015 elapsed 0 00:00:45
[oracle@dba expdir]$ expdp fei/fei directory=expdir dumpfile=fei.dmp logfile=sec.log EXCLUDE=TABLE:\"IN\(\'T1\',\'T2\'\)\"
Export: Release 11.2.0.4.0 - Production on Thu Feb 12 11:00: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.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "FEI"."SYS_EXPORT_SCHEMA_04": fei/******** directory=expdir dumpfile=fei.dmp logfile=sec.log EXCLUDE=TABLE:"IN('T1','T2')"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 832 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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
. . exported "FEI"."SYS_EXPORT_SCHEMA_01" 137.8 KB 1166 rows
. . exported "FEI"."SYS_EXPORT_SCHEMA_02" 137.8 KB 1166 rows
. . exported "FEI"."SYS_EXPORT_SCHEMA_03" 137.8 KB 1166 rows
. . exported "FEI"."T3" 5.015 KB 1 rows
Master table "FEI"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for FEI.SYS_EXPORT_SCHEMA_04 is:
/home/oracle/expdir/fei.dmp
Job "FEI"."SYS_EXPORT_SCHEMA_04" successfully completed at Thu Feb 12 11:00:57 2015 elapsed 0 00:00:24
排除表T1和T2后T3表被成功导出。
注意在Linux的bash下特殊字符的转义处理
使用PARFILE参数规避不同操作系统中特殊字符的转义
为了规避不同操作系统上特殊字符转义带来的麻烦，我们可以使用PARFILE参数规避一下这个难题。
vi fei.par
userid=fei/fei
directory=expdir
dumpfile=fei.dmp
logfile=fei.log
EXCLUDE=TABLE:"IN('T1','T2')"
expdp parfile=fei.par
在完成特殊条件导出时，推荐将需要的所有参数统一写到参数文件中。
9.小结
EXPDP工具与EXP相比不仅仅是效率上的提升，更重要的是功能上的增强。
本文中以EXPDP的EXCLUDE选项为例展示了此工具的便捷之处，善用之。
Good luck.
扫一扫
2071
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
