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