Total Pageviews

64,987

Sunday, 8 April 2012

Script to find export dumpfile version


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;
/

No comments:

Post a Comment