How to import Oracle DMP file without knowing the original tablespace?

Question

Use case: customer's will send their backup of their DB in a dmp file. That's all they will send.

I'm building a script that will import that dmp file into Oracle using DBMS_DATAPUMP. However, as I do not know the original tablespace name, I'm getting the following error: ORA-39083: Object type USER:"XXXXX" failed to create with error: ORA-00959: tablespace 'XXXXXXX' does not exist

This is my PL/SQL procedure:

    PROCEDURE IMPORTING 
(
  DMPFILES IN VARCHAR2,
  FROMSCHEMA IN VARCHAR2,
  TOSCHEMA IN VARCHAR2
) AS
  ind NUMBER;              -- Loop index
  h1 NUMBER;               -- Data Pump job handle
  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
  array apex_application_global.vc_arr2;
BEGIN
  h1 := DBMS_DATAPUMP.open('IMPORT','FULL',NULL,'EXAMPLE1'); 
  dbms_datapump.add_file(handle => h1, filename => 'IMPORT.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);
  -- usign this function to split the files passed as a String to an array
  array  := apex_util.string_to_table(DMPFILES, ',');

  for i in 1 .. array.count loop
    DBMS_DATAPUMP.ADD_FILE(h1,array(i),'DATA_PUMP_DIR');
  end loop;

  DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_SCHEMA',FROMSCHEMA,TOSCHEMA);

dbms_datapump.set_parameter(handle => h1, name =>
'INCLUDE_METADATA', value => 1);
 dbms_datapump.set_parameter(handle => h1, name =>
'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
 dbms_datapump.set_parameter(handle => h1, name =>
'REUSE_DATAFILES', value => 0);
 dbms_datapump.set_parameter(handle => h1, name =>
'SKIP_UNUSABLE_INDEXES', value => 0);

  DBMS_DATAPUMP.START_JOB(h1);

-- The import job should now be running. In the following loop, the job is 
-- monitored until it completes. In the meantime, progress information is 
-- displayed. Note: this is identical to the export example.

 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;

-- 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
        dbms_output.put_line(le(ind).LogText);
        ind := le.NEXT(ind);
      end loop;
    end if;
  end loop;

-- Indicate that the job finished and gracefully detach from it. 

  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(h1);
END IMPORTING;

So basically my question is: Is there a way to import a dmp file into Oracle without knowing the original tablespace...?

Thanks in advance for the help.

UPDATE: I found when using this script, I also need to know the schema name. So that will make the following changes on the script:

     PROCEDURE IMPORTING 
(
  DMPFILES IN VARCHAR2,
  FROMSCHEMA IN VARCHAR2,
  TOSCHEMA IN VARCHAR2,
  FROMTABLESPACE IN VARCHAR2,
  TOTABLESPACE IN VARCHAR2
) AS
  ind NUMBER;              -- Loop index
  h1 NUMBER;               -- Data Pump job handle
  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
  array apex_application_global.vc_arr2;
BEGIN
  h1 := DBMS_DATAPUMP.open('IMPORT','FULL',NULL,'EXAMPLE1'); 
  dbms_datapump.add_file(handle => h1, filename => 'IMPORT.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);
  -- usign this function to split the files passed as a String to an array
  array  := apex_util.string_to_table(DMPFILES, ',');

  for i in 1 .. array.count loop
    DBMS_DATAPUMP.ADD_FILE(h1,array(i),'DATA_PUMP_DIR');
  end loop;

  DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_TABLESPACE',FROMTABLESPACE,TOTABLESPACE);
  DBMS_DATAPUMP.METADATA_REMAP(h1,'REMAP_SCHEMA',FROMSCHEMA,TOSCHEMA);

dbms_datapump.set_parameter(handle => h1, name =>
'INCLUDE_METADATA', value => 1);
 dbms_datapump.set_parameter(handle => h1, name =>
'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
 dbms_datapump.set_parameter(handle => h1, name =>
'REUSE_DATAFILES', value => 0);
 dbms_datapump.set_parameter(handle => h1, name =>
'SKIP_UNUSABLE_INDEXES', value => 0);

  DBMS_DATAPUMP.START_JOB(h1);

-- The import job should now be running. In the following loop, the job is 
-- monitored until it completes. In the meantime, progress information is 
-- displayed. Note: this is identical to the export example.

 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;

-- 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
        dbms_output.put_line(le(ind).LogText);
        ind := le.NEXT(ind);
      end loop;
    end if;
  end loop;

-- Indicate that the job finished and gracefully detach from it. 

  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(h1);
END IMPORTING;

I tested that with a dmp that I exported so I knew the TableSpace and the Schema name.

Summary: any way then to know the tablespace name and the schema name from a dmp file?

Thanks.

the_ccalderon · Accepted Answer

Short answer: no

Long answer: yes if you generate the ddl file and then you can actually grep the fille and search for the tablespaces and users. See bellow the script to generate the DDL file:

create or replace 
PROCEDURE GENERATE_SQL 
(
  DMPFILES IN VARCHAR2 
, SQLFILENAME IN VARCHAR2 
, VERSIONPARAM IN VARCHAR2 
, JOBNAME IN VARCHAR2 
) AS 
  h1 number;
  array apex_application_global.vc_arr2;
  ind NUMBER;              -- Loop indexdasdads
  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    => 'SQL_FILE',
    job_mode     => 'SCHEMA',
    remote_link  =>  null,
    job_name     => JOBNAME,
    version      => VERSIONPARAM
  );
  array  := apex_util.string_to_table(DMPFILES, ',');
  for i in 1 .. array.count loop
    dbms_output.put_line('array(i): ' || array(i));
    DBMS_DATAPUMP.ADD_FILE(h1,array(i),'DBRAT_DMP',dbms_datapump.ku$_file_type_dump_file);
  end loop;
  dbms_output.put_line('datapump_job: ' || h1);

  dbms_datapump.add_file(
    handle    =>  h1,
    filename  => SQLFILENAME,
    directory => 'DBRAT_DMP',
    filetype  =>  dbms_datapump.ku$_file_type_sql_file);

  dbms_datapump.start_job(
    handle       => h1,
    skip_current => 0,
    abort_step   => 0);

  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;

-- 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
        dbms_output.put_line(le(ind).LogText);
        ind := le.NEXT(ind);
      end loop;
    end if;
  end loop;

-- Indicate that the job finished and gracefully detach from it. 

  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(h1);
END GENERATE_SQL;

How to import Oracle DMP file without knowing the original tablespace?

Answers (1)

Related Questions