I have a requirement where I have to refresh tables from PROD to DEV. But refresh needs to be done in such a manner that partitioned table should be refreshed with its latest top 10 partitions and non partitioned table should be refreshed full
I am using DBPUMP API to do this over network link. I was able to pull 2 partitions successfully for a single table but when I specify multiple data_filter lines second table never gets refreshed. And also where will I incorporate non partioned tables?
Request you to please have a look at the pattern of code I am using. For detecting partitions I have planned that I will write a code to fetch partition names and then append them in a code but if you have a better suggestion then please shoot that to me.
=====================================================================
set scan off
set serveroutput on
set escape off
DECLARE
h1 number;
errorvarchar varchar2(100):= 'ERROR';
tryGetStatus number := 0;
l_ku$status ku$_status1020;
l_job_state varchar2(4000);
percent_done NUMBER; -- Percentage of job complete
job_state VARCHAR2(30); -- To keep track of job state
le ku$_LogEntry; -- For WIP and error messages
js ku$_JobStatus; -- The job status from get_status
jd ku$_JobDesc; -- The job description from get_status
sts ku$_Status; -- The status object returned by get_status
begin
h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'TABLE', version => 'COMPATIBLE', remote_link =>'DBPUMP_TEST_LINK' );
tryGetStatus := 1;
dbms_datapump.set_parallel(handle => h1, degree => 1);
dbms_datapump.add_file(handle => h1, filename => 'POC-'||to_char(sysdate,'yyyy-mm-dd-hh24_mi_ss')||'.LOG', directory => 'DATAPUMP', filetype => 3);
dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1);
--dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_LIST', value =>'''FDW_PUMP_TEST''');
dbms_datapump.data_filter(handle => h1, name => 'PARTITION_LIST', value =>'SYS_P26447,SYS_P27786', TABLE_NAME => 'TAB1');
dbms_datapump.data_filter(handle => h1, name => 'PARTITION_LIST', value =>'SYS_P34155,SYS_P34666', TABLE_NAME => 'TAB2');
dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 0);
dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
DBMS_DATAPUMP.SET_PARAMETER(handle => h1,name => 'TABLE_EXISTS_ACTION',value => 'TRUNCATE');
dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
errorvarchar := 'NO_ERROR';
DBMS_OUTPUT.PUT_LINE('NO_ERROR');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI:SS'));
percent_done := 0;
job_state := 'UNDEFINED';
while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
dbms_datapump.get_status(h1,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,-1,job_state,sts);
js := sts.job_status;
-- If the percentage done changed, display the new value.
if js.percent_done != percent_done
then
dbms_output.put_line('*** Job percent done = ' ||
to_char(js.percent_done));
percent_done := js.percent_done;
end if;
end loop;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI:SS'));
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN
DBMS_DATAPUMP.DETACH(h1);
DBMS_OUTPUT.PUT_LINE('LANDED IN EXCEPTION');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END IF;
RAISE;
END;
RAISE;
END;
/
=====================================================================
Regards
Nishant