使用 Oracle Datapump API 实现数据导出
使用 Oracle Datapump API 实现数据导出
Oracle Datapump API 是基于PL/SQL实现的,是命令行方式下的补充。使用Datapump API可以将其逻辑备份特性将其集成到应用程序当中,基于界面来实现有利于简化其管理。本文主要描述的使用Datapump API描述各种不同情形的数据导出。
一、演示使用datapump api实现数据导出
[sql]- --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;
- /
- --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'
- , VALUE => ' NOT LIKE ''T%'' ');
- DBMS_DATAPUMP.start_job (l_dp_handle);
- DBMS_DATAPUMP.detach (l_dp_handle);
- END;
- /
- /**************************************************/
- /* Author: Robinson Cheng */
- /* Blog: http://blog.csdn.net/robinson-0612 */
- /* MSN: robinson_0612@hotmail.com */
- /* QQ: 645746311 */
- /**************************************************/
|
评论暂时关闭