Oracle Datapump: modify fully qualified objects when cloning a schema

datapumporacle-11g-r2

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

Best Answer

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

Related Question