Pavan DBA's Blog

The DBA Knowledge Store

Script to find export dumpfile version

Posted by Pavan DBA on July 13, 2011


The below script presented by one of Oracle expert in forums will help us to know dumpfile details like version etc. It also helps us in identifying if it is a classic export dumpfile or datapump dumpfile

 
set verify off
set define on
 
accept a_filename char prompt ‘Filename: ‘
accept a_directory char prompt ‘Directory: ‘
 
declare
  fn    varchar2(256) := ‘&&a_filename’;
  dir   varchar2(30) := ‘&&a_directory’;
 
  info  ku$_dumpfile_info;
  ft    number;
 
  h   number;
  js  dba_datapump_jobs.state%type;
 
begin
 
  sys.dbms_datapump.get_dumpfile_info(
                                      fn,
                                      dir,
                                      info,
                                      ft
   );
 
  if ft = 0 then
    dbms_output.put_line(‘File not recognized.’);
  elsif ft = 1 then
    –dbms_output.put_line(‘File is DataPump export file.’);
    /* can we attach it to a DP job? */
    begin
      h := dbms_datapump.open (
                                 ‘SQL_FILE’,
                                 ‘FULL’
                            );
 
      dbms_datapump.add_file (h,
                            ‘examine_’||fn||’.log’,
                            dir,
                            null, — job name
                            sys.dbms_datapump.ku$_file_type_log_file
                          );
 
      dbms_datapump.add_file (h,
                            fn,
                            dir,
                            null, — job name
                            sys.dbms_datapump.ku$_file_type_dump_file
                          ); 
 
      dbms_datapump.stop_job(h);                         
      dbms_output.put_line(‘File is DataPump export file.’);
    exception
      when dbms_datapump.INVALID_ARGVAL then
        dbms_output.put_line(‘File is ORACLE_DATAPUMP External table file.’);
        dbms_datapump.stop_job(h);
      when others then
        dbms_output.put_line(sqlerrm);
        dbms_datapump.stop_job(h);
    end;
   
   
   
  elsif ft = 2 then
    dbms_output.put_line(‘File is Classic export file.’);
  else
    dbms_output.put_line(‘Undocumented, file type is: ‘||to_char(ft));
  end if;
 
    /* appears that external tables are recognized as DataPump files but cannot be imported:
    ORA-39000: bad dump file specification
    ORA-31619: invalid dump file “C:\temp\DW_INSTR_CMPNT.DP”   
    */
 
end;
/

One Response to “Script to find export dumpfile version”

  1. […] Uwe Hesse Blog « Script to find export dumpfile version […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: