使用 Oracle Datapump 实现数据导出 - 数据库编程

/

--3、导出schema并过滤掉特定表(使用非当前帐户导出时应过滤schema)

DECLARE
l_dp_handle NUMBER;
BEGIN
l_dp_handle :=
DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'SCHEMA');
DBMS_DATAPUMP.
add_file (handle => l_dp_handle
, filename => 'scott_filter.dmp'
, directory => 'DB_DUMP_DIR'
, filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);
DBMS_DATAPUMP.
add_file (handle => l_dp_handle
, filename => 'scott_filter.log'
, directory => 'DB_DUMP_DIR'
, filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);
DBMS_DATAPUMP.
metadata_filter (handle => l_dp_handle
, name => 'SCHEMA_LIST'
, VALUE => ' ''SCOTT'' ');
DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle
, name => 'NAME_EXPR'
, VALUE => ' !=''EMP'' '
, object_type => 'TABLE');
DBMS_DATAPUMP.start_job (l_dp_handle);
END;
/

--4、导出当前schema下的所有表并过滤特定表

DECLARE
l_dp_handle NUMBER;
BEGIN
l_dp_handle :=
DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE');
DBMS_DATAPUMP.
add_file (handle => l_dp_handle

, filename => 'scott_filter_2.dmp'
, directory => 'DB_DUMP_DIR'
, filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);
DBMS_DATAPUMP.
add_file (handle => l_dp_handle
, filename => 'scott_filter_2.log'
, directory => 'DB_DUMP_DIR'
, filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);
DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle
, name => 'NAME_EXPR'
, VALUE => ' !=''EMP'' ');
DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle
, name => 'NAME_EXPR'
, VALUE => ' !=''DEPT'' ');
DBMS_DATAPUMP.start_job (l_dp_handle);
DBMS_DATAPUMP.detach (l_dp_handle);
END;
/

--5、批量过滤当前用户下的特定表

DECLARE
l_dp_handle NUMBER;
BEGIN
l_dp_handle :=
DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE');
DBMS_DATAPUMP.
add_file (handle => l_dp_handle
, filename => 'scott_filter_3.dmp'
, directory => 'DB_DUMP_DIR'
, filetype => DBMS_DATAPUMP.KU$FILE_TYPE_DUMP_FILE);
DBMS_DATAPUMP.
add_file (handle => l_dp_handle
, filename => 'scott_filter_3.log'
, directory => 'DB_DUMP_DIR'
, filetype => DBMS_DATAPUMP.KU$FILE_TYPE_LOG_FILE);
DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle
, name => 'NAME_EXPR'

使用 Oracle Datapump 实现数据导出(二)