联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
使用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; /
0RA-39001,报错,/* Source of is not available */还望高手指点,test中数据泵如下:declare
h1 number; –创建数据泵工作句柄
begin
h1:=dbms_datapump.open(operation=>’export’,job_mode => ‘schema’);–建立一个用户定义的数据泵做schema的备份
–定义一个存储文件
dbms_datapump.add_file(handle=>h1,filename => ‘F:/oracle/product/10.2.0/admin/orcl/dpdump/scott1.dmp’);
–一个过滤条件
dbms_datapump.metadata_filter(handle=>h1,name => ‘schema_expr’,value => ‘in”SCOTT”’);
–启动数据泵
dbms_datapump.start_job(handle =>h1);
–断开数据泵回话
dbms_datapump.detach(handle=>h1);
end;