oracle datapump job,使用dbms_pumpdata执行expdp操作

使用dbms_pumpdata执行expdp操作

set serverout on

declare

h1 number; -- Datapump handle

dir_name varchar2(30); -- Directory Name

job_status VARCHAR2(30);

begin

dir_name := 'DATA_PUMP_DIR';

h1 := dbms_datapump.open(

operation =>'EXPORT', --是export还是impport

--导出表配置

job_mode =>'TABLE', --job_mode可以为SCHEMA/TABLE等

--导出用户配置

job_mode =>'SCHEMA',

remote_link => NULL, --是否使用dblink导出(就是NETWORK_LINK)

job_name =>'TABLE_XFF' --job_name expdpjob的名称

);

dbms_datapump.add_file(handle =>h1,

filename => 'XIFENFEI.DMP',

directory => dir_name,

filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE,

reusefile => 1); -- value of 1 instructs to overwrite existing file

dbms_datapump.add_file(handle =>h1,

filename => 'XIFENFEI.LOG',

directory => dir_name,

filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,

reusefile => 1);

--导出表配置

dbms_datapump.metadata_filter(handle =>h1,

name => 'TABLE_FILTER',

value => 'CHF.T_XIFENFEI');

--导出用户配置

dbms_datapump.metadata_filter (handle => dp_handle,

name => 'SCHEMA_EXPR',

value => 'IN (''CHF'')');

-- Start the job.

dbms_datapump.start_job(h1);

dbms_datapump.wait_for_job (handle => dp_handle,

job_state => job_status);

dbms_output.put_line ('DataPump Export - '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS')||' Status '||job_status);

begin

dbms_datapump.detach(handle => h1);

end;

end;

/