0

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.

  • I have never seen that happen. Are you sure it's datapump and not something else in your procedure that's doing it? What version are you using? – Mat Jul 29 '14 at 5:24
1

it never happens. You should check your script. If you are using impdp utility, there is no option for deleting the file after the import is done.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.