1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
|
--Oracle 11.1.0.7
set serveroutput on size 100000
declare
V_DMP_NAME constant VARCHAR2( 400 ) := 'THIS_YEAR';
V_START_DATE constant DATE := trunc(sysdate,'YEAR');
V_SOURCE_SCHEMA constant VARCHAR2(30):='TEST';
V_DIRECTORY constant VARCHAR2(30) := 'DATA_PUMP_DIR';
--
V_HANDLE NUMBER;
job_state VARCHAR2( 400 );
V_DATE_STR VARCHAR2(400 ) := 'to_date('''||to_char(V_START_DATE,'DD/MM/YYYY')||''',''DD/MM/YYYY'')';
begin
V_HANDLE := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => V_DMP_NAME ||'_EXP', version => 'COMPATIBLE');
dbms_datapump.add_file(handle => V_HANDLE, filename => V_DMP_NAME || '.DMP', directory => V_DIRECTORY, filetype => dbms_datapump.KU$_FILE_TYPE_DUMP_FILE );
dbms_datapump.add_file(handle => V_HANDLE, filename => V_DMP_NAME ||'_EXPORT.LOG', directory => V_DIRECTORY, filetype => dbms_datapump.KU$_FILE_TYPE_LOG_FILE);
dbms_datapump.metadata_filter(handle => V_HANDLE, name => 'SCHEMA_EXPR', value => 'IN('''||V_SOURCE_SCHEMA||''')');
--une table parente:
dbms_datapump.data_filter( handle=>V_HANDLE ,name=>'SUBQUERY' ,schema_name=> V_SOURCE_SCHEMA ,table_name=>'ORDER',value=>
'where ORDER_ID in (select CO.ORDER_ID from '||V_SOURCE_SCHEMA||'.ORDER CO '
||' where CO.CREATE_TIME >= '|| V_DATE_STR || ' and not exists '
||' ( select 1 from '||V_SOURCE_SCHEMA||'.ORDER CO2 where CO2.ORDER_ID = CO.PARENT_ORDER_ID and CO2.CREATE_TIME < '|| V_DATE_STR
||'))'
);
--une table fille
dbms_datapump.data_filter( handle=>V_HANDLE ,name=>'SUBQUERY' ,schema_name=> V_SOURCE_SCHEMA ,table_name=>'ITEM',value=>
'where ITEM_ID in (select IT.ITEM_ID from '||V_SOURCE_SCHEMA||'.ITEM IT where exists'
||'(select 1 from '||V_SOURCE_SCHEMA||'.ORDER CO where CO.ORDER_ID = IT.ORDER_ID and CO.CREATE_TIME >='||V_DATE_STR
||' and not exists '
||'(select 1 from '||V_SOURCE_SCHEMA||'.ORDER CO2 where CO2.ORDER_ID = CO.PARENT_ORDER_ID and CO2.CREATE_TIME <' ||V_DATE_STR ||')'
||'))'
);
--
dbms_datapump.start_job(handle => V_HANDLE, skip_current => 0, abort_step => 0);
dbms_datapump.wait_for_job(handle => V_HANDLE , job_state=> job_state );
dbms_output.put_line( 'job_state= '|| job_state );
end;
/ |
Partager