基于界面来实现有利于简化其管理。本文主要描述的使用Datapump API描述各种不同情形的数据导出。
一、演示使用datapump api实现数据导出
[sql] view plaincopyprint
--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
--sepcified operation,job mode
l_dp_handle :=
DBMS_DATAPUMP.open (operation => 'EXPORT'
, job_mode => 'SCHEMA'
, remote_link => NULL
, job_name => 'JOB_EXP1'
, version => 'LATEST');
--specified dumpfile and dump directory
DBMS_DATAPUMP.
add_file (handle => l_dp_handle
, filename => 'scott_schema.dmp'
, directory => 'DB_DUMP_DIR'
, filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);
--specified log file and dump directory
DBMS_DATAPUMP.
add_file (handle => l_dp_handle
, filename => 'scott_schema.log'
, directory => 'DB_DUMP_DIR'
, filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);
--specified fliter for schema
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
, filename => 'emp_tbl.log'
, directory => 'DB_DUMP_DIR'
, filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);
-->如果非当前帐户，使用下面的过滤条件，即特定schema下的特定表，如为当前帐户，此过滤条件可省略
DBMS_DATAPUMP.
metadata_filter (handle => l_dp_handle
, name => 'SCHEMA_EXPR'
, VALUE => 'IN(''SCOTT'')');
DBMS_DATAPUMP.
metadata_filter (handle => l_dp_handle
, name => 'NAME_EXPR'
, VALUE => 'IN(''EMP'')');
DBMS_DATAPUMP.start_job (l_dp_handle);
DBMS_DATAPUMP.detach (l_dp_handle);
END;