I'm working on a PLSQL script to import a Oracle datapump dumpfile using the dbms_datapump package. Each time the script has succesfully imported the contents of the dumpfile I notice that the dumpfile has been removed from the directory.
I would like the operator control the removal of the dumpfile just in case the script fails somewhere.
Haven't been able to find any reference to this behavior in the documentation and/or a parameter to control it.
Ok, here is some code. It runs on Oracle 11.2.0.4.
The main script I run is this:
begin
ngm_datapump_import_pck.verwerk_dumpfile(p_directory => 'NGM40_PAD_EXPORT'
,p_dumpfile => 'EXPORT_NGM40.DMP'
,p_hoofdschema => 'NGM40'
,p_datasets => 'NGM4001,NGM4002');
dbms_output.put_line(dbms_utility.get_time);
loop
exit when ngm_datapump_import_pck.fileexists('NGM40_PAD_EXPORT', 'EXPORT_NGM40.DMP') = 0;
end loop;
dbms_output.put_line(dbms_utility.get_time);
end;
It calls a packaged procedure that executes the import. When it is done I check if the dumpfile still exists. Here is an example of the output.
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Job "NGM40"."IMPORT_NGM40" completed with 311 error(s) at Wed Jul 30 09:57:47 2014 elapsed 0 00:01:04
Job has completed
Final job state = COMPLETED
File Exists :1
File Exists :1
Import gereed
File Exists :1
18328185
18328189
As you can see the file still exists when the import is done but only a fragment of a second later it's gone. In some other runs the file is gone a lot faster.
Here is the code of the import procedure.
procedure verwerk_dumpfile(p_directory varchar2
,p_dumpfile varchar2
,p_hoofdschema varchar2
,p_datasets varchar2) is
h1 number; -- Data Pump job handle
l_job_status varchar2(30); -- To keep track of job state
l_status ku$_status;
l_schema_lijst varchar2(32767);
ind number; -- Loop index
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
sts ku$_status; -- The status object returned by get_status
l_statement varchar2(32767);
i pls_integer;
l_schema varchar2(30);
type t_curtype is ref cursor;
l_cursor t_curtype;
l_dst_id pls_integer;
l_dst_naam varchar2(100);
begin
os_user_sendmessage('Start import dumpfile');
os_user_sendmessage('directory : ' || p_directory);
os_user_sendmessage('dumpfile : ' || p_dumpfile);
os_user_sendmessage('hoofdschema : ' || p_hoofdschema);
os_user_sendmessage('datasets : ' || p_datasets);
os_user_sendmessage('Start import job');
os_user_sendmessage('File Exists :' || fileexists(p_directory, p_dumpfile));
h1 := dbms_datapump.open(operation => 'IMPORT'
,job_mode => 'SCHEMA'
,job_name => 'IMPORT_' || p_hoofdschema
,version => 'COMPATIBLE');
--os_user_sendmessage('Voeg dumpfile toe');
dbms_datapump.add_file(handle => h1, filename => p_dumpfile, directory => p_directory);
os_user_sendmessage('Voeg logfile toe :' || p_dumpfile || '.log');
dbms_datapump.add_file(handle => h1
,filename => p_dumpfile || '.log'
,directory => p_directory
,filetype => dbms_datapump.ku$_file_type_log_file);
l_schema_lijst := '''' || p_hoofdschema || ''',''' || replace(p_datasets, ',', ''',''') || '''';
os_user_sendmessage('Te importeren schema''s :' || l_schema_lijst);
dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_LIST', value => l_schema_lijst);
-- os_user_sendmessage('Exclude files');
dbms_datapump.metadata_filter(handle => h1
,name => 'NAME_EXPR'
,value => 'not in (''NGM_MONITOR'',''APEX_ACCESS_CONTROL'',''APEX_ACCESS_SETUP'',''NGM_DIRECTORY'')'
,object_path => 'TABLE');
dbms_datapump.metadata_filter(handle => h1
,name => 'NAME_EXPR'
,value => 'not in (''NGM_DATAPUMP_IMPORT_PCK'')'
,object_path => 'PACKAGE');
os_user_sendmessage('Set parameter');
dbms_datapump.set_parameter(handle => h1, name => 'TABLE_EXISTS_ACTION', value => 'REPLACE');
os_user_sendmessage('Start job');
dbms_datapump.start_job(handle => h1);
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
os_user_sendmessage('*** Job percent done = ' || to_char(js.percent_done));
percent_done := js.percent_done;
end if;
-- If any work-in-progress (WIP) or error messages were received for the job,
-- display them.
if (bitand(sts.mask, dbms_datapump.ku$_status_wip) != 0)
then
le := sts.wip;
else
if (bitand(sts.mask, dbms_datapump.ku$_status_job_error) != 0)
then
le := sts.error;
else
le := null;
end if;
end if;
if le is not null
then
ind := le.first;
while ind is not null
loop
if instr(le(ind).logtext, 'ORA-31684') = 0
and instr(le(ind).logtext, 'ORA-39111') = 0
then
os_user_sendmessage(le(ind).logtext);
end if;
ind := le.next(ind);
end loop;
end if;
end loop;
-- Indicate that the job finished and detach from it.
os_user_sendmessage('Job has completed');
os_user_sendmessage('Final job state = ' || job_state);
os_user_sendmessage('File Exists :' || fileexists(p_directory, p_dumpfile));
dbms_datapump.detach(h1);
os_user_sendmessage('File Exists :' || fileexists(p_directory, p_dumpfile));
os_user_sendmessage('Import gereed');
os_user_sendmessage('File Exists :' || fileexists(p_directory, p_dumpfile));
exception
when others then
os_user_sendmessage(sqlerrm);
-- verwijder job indien noodzakelijk
begin
os_user_sendmessage('Zoek nog lopende job');
dbms_datapump.get_status(h1, dbms_datapump.ku$_status_job_status, 0, l_job_status, l_status);
os_user_sendmessage('Job gevonden met status ' || l_job_status);
dbms_datapump.stop_job(h1, 1, 0);
os_user_sendmessage('Job gestopt');
exception
when others then
os_user_sendmessage('Import afgesloten met fouten');
end;
raise;
end;
os_user_sendmessage produces output to a logging system.
In a simplified version of the script I can not reproduce the removal of the file.
