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
