日期: 2016-02-22

用datapump api来导出表名和字段名区分大小写的数据

由于导出的数据中表名区分大小,需要query的字段包含大小,写expdp的par文件很困难,顺便就学习了一下dbms_datapump包导出数据。
写了一个存储过程,然后用job每天定时导出数据:

create or replace procedure expdp_data
is
h1 number;
v_date varchar2(200);
begin
select to_char(sysdate-1,’YYYYMMDD-HH24:MI’) into v_date from dual;
h1 := dbms_datapump.open(‘EXPORT’,'TABLE’);
sys.dbms_datapump.add_file(h1,v_date||’_%U.dmp’,'EXP_DIR’,NULL,DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE,1);
sys.dbms_datapump.add_file(h1,v_date,’EXP_DIR’,NULL,DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
sys.dbms_datapump.metadata_filter(handle => h1,name => ‘NAME_EXPR’,value => ‘IN (”Oracle_Tablespaces_64”,”Oracle_Tablespaces_64_2”)’,object_type => ‘TABLE’);
sys.dbms_datapump.data_filter(handle => h1,name => ‘SUBQUERY’,value => ‘WHERE substr(“Hub_Timestamp”,2,6)=to_char(sysdate-2,”YYMMDD”)’,table_name=>’Oracle_Tablespaces_64′);
sys.dbms_datapump.data_filter(handle => h1,name => ‘SUBQUERY’,value => ‘WHERE substr(“Hub_Timestamp”,2,6)=to_char(sysdate-3,”YYMMDD”)’,table_name=>’Oracle_Tablespaces_64_2′);
sys.dbms_datapump.set_parameter(h1,’CLIENT_COMMAND’,'EXP Oracle_Tablespaces_64 data’);
sys.dbms_datapump.set_parallel(h1,2);
sys.dbms_datapump.start_job(h1);
sys.dbms_datapump.detach(h1);
DBMS_OUTPUT.PUT_LINE(v_date||”’s date has exported’);
end;
/

阅读详细 »