演示环境
scott@CNMMBO> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
1、导出schema(schema模式)
DECLARE
l_dp_handle NUMBER;
l_last_job_state VARCHAR2 (30) := 'UNDEFINED';
l_job_state VARCHAR2 (30) := 'UNDEFINED';
l_sts KU$STATUS;
BEGIN
l_dp_handle :=
DBMS_DATAPUMP.open (operation => 'EXPORT'
, job_mode => 'SCHEMA'
, remote_link => NULL
, job_name => 'JOB_EXP1'
, version => 'LATEST');
DBMS_DATAPUMP. add_file (handle => l_dp_handle
, filename => 'scott_schema.dmp'
, directory => 'DB_DUMP_DIR'
, filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);
DBMS_DATAPUMP. add_file (handle => l_dp_handle
, filename => 'scott_schema.log'
, directory => 'DB_DUMP_DIR'
, filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);
DBMS_DATAPUMP. metadata_filter (handle => l_dp_handle
, name => 'SCHEMA_EXPR'
, VALUE => 'IN (''SCOTT'')');
DBMS_DATAPUMP.start_job (l_dp_handle);
DBMS_DATAPUMP.detach (l_dp_handle);
END;
/
2、导出特定表table(表模式)
DECLARE
l_dp_handle NUMBER;
l_last_job_state VARCHAR2 (30) := 'UNDEFINED';
l_job_state VARCHAR2 (30) := 'UNDEFINED';
l_sts KU$STATUS;
BEGIN
l_dp_handle :=
DBMS_DATAPUMP.open (operation => 'EXPORT'
, job_mode => 'TABLE'
, remote_link => NULL
, job_name => 'JOB_EXP2'
, version => 'LATEST');
DBMS_DATAPUMP. add_file (handle => l_dp_handle
, filename => 'emp_tbl.dmp'
, directory => 'DB_DUMP_DIR'
, filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);
DBMS_DATAPUMP. add_file (handle => l_dp_handle