I am confronted with a problem for which I have not found an elegant solution:

I need to clone a schema within the same database, so I use datapump's remap_schema import parameter.

The problem is that the PL/SQL Packages contain lots of fully qualified object names (such as select * from FROM_SCHEMA_A.table_name. These qualifiers are cloned. This makes the cloned package invalid.

I could obviously grant all on FROM_SCHEMA_A ... to TO_SCHEMA_A to make the problem go, but this is not what I want. I'd rather want the qualified names to change as well.

The export parameters are


The import parameters are

  • 1
    It's a pity that location transparency is broken by the prefixing. Normally synonyms would be used to solve this and make your task possible. – ik_zelf Nov 10 '13 at 18:27

if you grant privileges (as you mention above), the problem may seem to go away, but your code executing in SCHEMA_B may be accessing data from tables in SCHEMA_A, unless that is what you want.

Unfortunately there are no easy answers to this problem, since remap_schema option of datapump doesn't really support what you are trying to do. However you may try following option (I have done it before),

  1. Extract all plsql code and/or view definition (datapump can do this for you using impdp with sqlfile option),
  2. use sed or any other tool of your choice and replace SCHEMA_A. to SCHEMA_B. where you see objects prefixed. Be careful, occasionally unwanted lines might get changed, so you will have to track that and implement workarounds.
  3. impdp tables,indexes,constraints to SCHEMA_B
  4. using sqlplus connect to SCHEMA_B and run the modified scripts from step 2 to load all plsql code, create views and other dependent objects etc.

It is cumbersome, but short of fixing your source schema, I don't think you have too many choices. Luckily if you are in a situation where code doesn't change that often, you can hold on to converted files and avoid these manual steps until something breaks.

Oh btw, if your pl/sql code looks like "create or replace schema_a.package_name", bad luck, remap_schema won't support that either (until 11.2, I have not tested in 12c yet).

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.