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