Data Pump的导入和导出数据（ORA-31684）

1.创建Directory对象，并授予相应用户读写权限：
SQL> create directory dump_file_dir as ‘/u01/imp_exp/dmp‘
  2  ;

Directory created.

SQL> grant read,write on directory dump_file_dir to scott;

Grant succeeded.
但是物理文件却……
[oracle@linux5 imp_exp]$ ls
dept_emp.dmp  dept_emp.log  fulldb.dmp  fulldb.log  scott_to_test.log
应该会报错……
[oracle@linux5 orcl]$ expdp scott/oracle directory=dump_file_dir dumpfile=dept_10.dmp nologfile=y tables=de% query=dept:\"where deptno=\‘10\‘\"

Export: Release 10.2.0.1.0 - Production on Thursday, 10 April, 2014 18:56:30

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/u01/imp_exp/dmp/dept_10.dmp"
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
提示不能创建文件：
[oracle@linux5 imp_exp]$ mkdir dmp
[oracle@linux5 imp_exp]$ ls
dept_emp.dmp  dept_emp.log  dmp  fulldb.dmp  fulldb.log  scott_to_test.log
再次导出：
[oracle@linux5 orcl]$ expdp scott/oracle directory=dump_file_dir dumpfile=dept_10.dmp nologfile=y tables=de% query=dept:\"where deptno=\‘10\‘\"

Export: Release 10.2.0.1.0 - Production on Thursday, 10 April, 2014 18:57:50

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dump_file_dir dumpfile=dept_10.dmp nologfile=y tables=de% query=dept:"where deptno=‘10‘"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
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
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.593 KB       1 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u01/imp_exp/dmp/dept_10.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 19:00:11
参数文件方式：
dept_exp_20.par：
directory=dump_file_dir
dumpfile=dept_exp_20.dmp
logfile=dept_exp_20.log
#include=table:"like ‘de%‘" 注意转义字符
query=dept:"where deptno=‘20‘"
~                                                                              
~                                     
[oracle@linux5 dmp]$ expdp scott/oracle parfile=dept_exp_20.par

Export: Release 10.2.0.1.0 - Production on Thursday, 10 April, 2014 19:14:28

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** parfile=dept_exp_20.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 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/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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.585 KB       1 rows
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u01/imp_exp/dmp/dept_exp_20.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:14:53
跨数据库链式方式导出数据：
在Data Pump Export中的Expdp：将远端数据库中的数据保存到本地的Dump文件中

创建Directory：
[oracle@localhost u01]$ mkdir dmp
[oracle@localhost u01]$ ls
app  arch_ldg  arch_orclstd  arch_std  dg  dmp  oracle10g  pfile
SQL> conn system/oracle@orcls_192.168.1.223
Connected.
SQL> create directory dump_dir as ‘/u01/dmp‘;

Directory created.

SQL> grant read,write on directory dump_dir to scott;

Grant succeeded.

SQL> create public database link expdp_link connect to scott identified by oracle using ‘orcl_192.168.1.222‘;  //必须是Public的，这样才会被别的用户识别，要不然报错。

Database link created.
[oracle@localhost ~]$ expdp scott/oracle network_link=expdp_link directory=dump_dir dumpfile=scott_schema.bak nologfile=y

Export: Release 10.2.0.1.0 - Production on Thursday, 10 April, 2014 20:27:09

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** network_link=expdp_link directory=dump_dir dumpfile=scott_schema.bak nologfile=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 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/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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.656 KB       4 rows
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u01/dmp/scott_schema.bak
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:28:04

[root@localhost dmp]# ls
scott_schema.bak

重定义对象所属SCHEMA和表空间（REMAP）
[oracle@linux5 imp_exp]$ expdp scott/oracle directory=dump_file_dir dumpfile=scott.dmp logfile=scott.log

Export: Release 10.2.0.1.0 - Production on Saturday, 12 April, 2014 18:16:21

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=dump_file_dir dumpfile=scott.dmp logfile=scott.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 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/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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.656 KB       4 rows
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u01/imp_exp/dmp/scott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:17:23
把SCOTT用户中的数据导入到TEST用户下：
SQL> conn scott/oracle
Connected.
SQL> select username,default_tablespace from user_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT                          USERS

SQL> conn test/oracle
Connected.
SQL> select username,default_tablespace from user_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST                           TEST

[oracle@linux5 imp_exp]$ impdp test/oracle schemas=scott directory=dump_file_dir dumpfile=scott.dmp nologfile=y remap_schema=scott:test remap_tablespace=users:test table_exists_action=replace

Import: Release 10.2.0.1.0 - Production on Saturday, 12 April, 2014 18:38:32

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_SCHEMA_01":  test/******** schemas=scott directory=dump_file_dir dumpfile=scott.dmp nologfile=y remap_schema=scott:test remap_tablespace=users:test table_exists_action=replace
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."DEPT"                               5.656 KB       4 rows
. . imported "TEST"."EMP"                                7.820 KB      14 rows
. . imported "TEST"."SALGRADE"                           5.585 KB       5 rows
. . imported "TEST"."BONUS"                                  0 KB       0 rows
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/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TEST"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 18:38:42

错误：ORA-31684: Object type USER:"TEST" already exists

解决： 加上参数：exclude跳过指定的对象类型
[oracle@linux5 imp_exp]$ impdp test/oracle exclude=user directory=dump_file_dir dumpfile=scott.dmp nologfile=y remap_schema=scott:test remap_tablespace=users:test

Import: Release 10.2.0.1.0 - Production on Saturday, 12 April, 2014 18:53:13

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01":  test/******** exclude=user directory=dump_file_dir dumpfile=scott.dmp nologfile=y remap_schema=scott:test remap_tablespace=users:test
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/TABLE_DATA
. . imported "TEST"."DEPT"                               5.656 KB       4 rows
. . imported "TEST"."EMP"                                7.820 KB      14 rows
. . imported "TEST"."SALGRADE"                           5.585 KB       5 rows
. . imported "TEST"."BONUS"                                  0 KB       0 rows
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/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at 18:53:18
目标用户不存在可以有IMPDP自动创建
[oracle@linux5 imp_exp]$ impdp system/oracle directory=dump_file_dir dumpfile=scott.dmp nologfile=y remap_schema=scott:test2

Import: Release 10.2.0.1.0 - Production on Saturday, 12 April, 2014 19:10:32

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dump_file_dir dumpfile=scott.dmp nologfile=y remap_schema=scott:test2
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/TABLE_DATA
. . imported "TEST2"."DEPT"                              5.656 KB       4 rows
. . imported "TEST2"."EMP"                               7.820 KB      14 rows
. . imported "TEST2"."SALGRADE"                          5.585 KB       5 rows
. . imported "TEST2"."BONUS"                                 0 KB       0 rows
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/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 19:10:40

创建之后的用户最好立即修改密码：
SQL> alter user test2 identified by oracle;

User altered.

SQL> alter user test2 account unlock;

User altered.

SQL> grant connect,resource to test2;

Grant succeeded.

SQL> conn test2/oracle
Connected.
SQL> select tname from tab;

TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE

SQL> select username,default_tablespace from user_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST2                          USERS

DBLINK：
在Data Pump Import中的Impdp：将远端数据库中的数据导入到本地的数据库
SQL> create public database link impdp_link connect to scott identified by oracle using ‘orcl_192.168.1.222‘;

Database link created.
SQL> select username,default_tablespace from user_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST                           USERS

SQL> select table_name,tablespace_name from user_tables;

no rows selected

[oracle@localhost ~]$ impdp test/oracle network_link=impdp_link nologfile=y remap_schema=scott:test

Import: Release 10.2.0.1.0 - Production on Saturday, 12 April, 2014 19:42:15

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_IMPORT_SCHEMA_01":  test/******** network_link=impdp_link nologfile=y remap_schema=scott:test
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "TEST"."DEPT"                                    4 rows
. . imported "TEST"."EMP"                                    14 rows
. . imported "TEST"."SALGRADE"                                5 rows
. . imported "TEST"."BONUS"                                   0 rows
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/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TEST"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 19:42:58

ORA-31684: Object type USER:"TEST" already exists
这个不用管，提示用户已存在……

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           USERS
EMP                            USERS
BONUS                          USERS
SALGRADE                       USERS

使用DBMS_DATAPUMP导出指定SCHEMA：
SQL> conn scott/oracle
Connected.
SQL> set serveroutput on

declare
 hand number;
 ind number;
 job_state varchar(20);
 le ku$_LogEntry;
 sts ku$_Status;
begin
 --create export task
 hand:=Dbms_DataPump.Open(Operation => ‘EXPORT‘,
			  job_mode  => ‘SCHEMA‘,
			  job_name  => ‘SCHEMA_SCOTT_EXPJOB‘);
 --add log file
 Dbms_DataPump.Add_File(handle    => hand,
			filename  => ‘SCOTT_expdp.log‘,
			directory => ‘DUMP_FILE_DIR‘,
			filetype  => 3);
 --add dump file
 Dbms_DataPump.Add_File(handle    => hand,
                        filename  => ‘SCOTT_expdp.dmp‘,
                        directory => ‘DUMP_FILE_DIR‘,
                        filetype  => 1);
 --start task
 Dbms_DataPump.Start_Job(hand);
 --Executing State
 job_state:=‘UNDEFINED‘;
 while(job_state!=‘COMPLETED‘) and (job_state!=‘STOPPED‘) loop
 dbms_datapump.get_status(hand,
			  dbms_datapump.ku$_status_job_error + 
			  dbms_datapump.ku$_status_job_status +
			  dbms_datapump.ku$_status_wip,
			  -1,
			  job_state,
			  sts);
 --print error
if(bitand(sts.mask,dbms_datapump.ku$_status_wip)!=0) then
 le := sts.wip;
 else
  if(bitand(sts.mask,dbms_datapump.ku$_status_job_error)!=0) then
 le := sts.error;
 else
 le :=null;
 end if;
end if;
if le is not null then
 ind := le.FIRST;
 while ind is not null loop
 dbms_output.put_line(le(ind).LogText);
 ind := le.NEXT(ind);
  end loop;
 end if;
end loop;
--print completed!
dbms_output.put_line(‘Job has completed!‘);
dbms_output.put_line(‘Final job state=‘||job_state);
dbms_datapump.detach(hand);
end;
/

Starting "SCOTT"."SCHEMA_SCOTT_EXPJOB":
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 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/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/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.656 KB       4 rows
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SCHEMA_SCOTT_EXPJOB" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SCHEMA_SCOTT_EXPJOB is:
/u01/imp_exp/dmp/SCOTT_expdp.dmp
Job "SCOTT"."SCHEMA_SCOTT_EXPJOB" successfully completed at 20:24:39
Job has completed!
Final job state=COMPLETED

PL/SQL procedure successfully completed.
[oracle@linux5 dmp]$ ls
dept_10.dmp        dept_exp_20.dmp  scott.dmp        SCOTT_expdp.log
dept_exp_20_2.log  dept_exp_20.log  SCOTT_expdp.dmp  scott.log
使用DBMS_DATAPUMP导出指定对象： 

如果名字不大写：
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2926
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3474
ORA-06512: at line 16

SQL> conn scott/oracle
Connected.
SQL> set serveroutput on
declare
 hand number;
 ind number;
 job_state varchar(20);
 le ku$_LogEntry;
 sts ku$_Status;
begin
  hand:= Dbms_DataPump.Open(operation => ‘EXPORT‘,
                          job_mode => ‘TABLE‘,
                          job_name => ‘SCOTT_TBL_EXPJOB3‘);
 Dbms_DataPump.MetaData_Filter(handle => hand,
                                 name => ‘NAME_EXPR‘,
                                value => ‘like (‘‘DE%‘‘)‘);
 Dbms_DataPump.Data_Filter(handle => hand,
                        name => ‘SUBQUERY‘,
 --                       value => ‘where dname = ‘‘SALES‘‘‘,
                        value => ‘where deptno = 10‘,
                        table_name => ‘DEPT‘);
 Dbms_DataPump.Add_File(handle => hand,
                        filename => ‘SCOTT_table_expdp.log‘,
                        directory => ‘DUMP_FILE_DIR‘,
                        filetype => 3);
 Dbms_DataPump.Add_File(handle => hand,
                        filename => ‘SCOTT_table_expdp.dmp‘,
                        directory => ‘DUMP_FILE_DIR‘,
                        filetype => 1);
Dbms_DataPump.Start_job(hand);
end;
[oracle@linux5 dmp]$ ls
123.dmp      dept_exp_20_2.log  dept_exp_20.log  scott.log              SCOTT_table_expdp.log
dept_10.dmp  dept_exp_20.dmp    scott.dmp        SCOTT_table_expdp.dmp

Data Pump的导入和导出数据（ORA-31684）,布布扣,bubuko.com

Data Pump的导入和导出数据（ORA-31684）

原文：http://blog.csdn.net/u013827322/article/details/23616037