Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

content=metadata_only
directory=DATAPUMP_DIR
dumpfile=clone_schema.dmp
schemas=from_schema_a
exclude=trigger

The import parameters are

directory=DATAPUMP_DIR
dumpfile=clone_schema.dmp
remap_schema=from_schema_a:to_schema_a
transform=OID:n
share|improve this question
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
add comment

1 Answer 1

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.