2 Replies Latest reply: Mar 15, 2011 11:13 AM by 847443 RSS

    expdp with remap_schema?

    847443
      My Oracle version is 11.2.0.1.0.

      I'm using impdp and expdp from batch files that ease the use of import and export of databases for our developers.
      To provide that every developer has his own schema on an oracle server, we use schema (user) names like
      product_username_version.
      The batch files ease the import and export so that the developer only has to type:
      restoredatabase <his_dumpfile>
      or
      backupdatabase <his_dumpfile>.

      Schema/user names are determined by the batch file.

      Now how can the batchfiles generically handle the import of another developer's dumpfile? The schema name is
      stored within the dumpfile and a developer should use some sort of remapping. But that is individual for every developer.
      How can i specify this? The batchfile does not know which schema name is in the dumpfile that should be imported.

      It would be great to specify a remap of schema during export with expdp - but i did not find anything like that.


      The impdp command that i'm calling from a batch file (Windows) is:

      impdp.exe product_username_version/password1 job_name=impdp_oracle11g
      directory=data_pump_dir dumpfile=test.Ora11g.de-DE.dmp
      table_exists_action=replace exclude=USER:"='product_username_version'",
      PACKAGE_BODY:"='HELPERS_PKG'"
      logfile=impdp.test.Ora11g.de-DE.dmp.log
      remap_schema=product:product_username_version

      As the batch file should be used for every user (without specifying a schema name would be great), it would be
      cool if the remap_schema could be specified generically - the used schema name contains the user specific username part.

      So i thought it would be great if every user could use expdp and maps his specific schema name "product_username_sw" to a
      generic one like "product" during export. But that doesn't seem possible?

      So at the moment i'm importing a dumpfile that does not contain a schema called "product" and i'm getting
      the ORA-39146 = schema does not exist - error.

      A specification like remap_schema=*:product_username_version for impdp would also be great. Is something like that possible?

      thanks a lot,
      Carsten
        • 1. Re: expdp with remap_schema?
          fjfranken
          844440 wrote:

          A specification like remap_schema=*:product_username_version for impdp would also be great. Is something like that possible?
          No that is not possible, as it would import all object of all schema's in your dumpfile into the 'product_username_version schema.

          This calls for a clear procedure, your developers need to work with, you could e.g. tell them to format the dumpfile in a certain syntax that contains the schema_name


          HTH
          FJFranken
          • 2. Re: expdp with remap_schema?
            847443
            And there's no possibility for the batch to get the schema name from the dumpfile?