How to replace and overwrite all existing objects in Oracle with impdp for full database import?

Question

I want to replace all the existing objects in my database, not just tables, into my current database.

I ran expdp with the parameter full=yes as the system user, who I granted datapump_imp_full_database while logged in as sysdba, from the instance I am importing from. I ran impdp on the target database with the parameter table_exists_action=replace, but (understandably) only existing tables were replaced, but procedures, functions, views were not.

Is there an equivalent of table_exists_action for all objects? If not, how can I achieve this?

Both databases are Oracle 12c on Windows 10.

Answer 1

There is one trap - if you drop schema - you lost all grants to other schema objects that schema had - and some of vievs/procedures/functions which depends on objects in other schemas - will not compile.

So - before you drop - you should prepare some scripts for rebuilding that object grants.

select * from dba_tab_privs where grantee='<schema-for-drop/import>'

Answer 2

I don't think there is a "Replace" option for non-table objects like procedures, packages etc.

The best option would be to drop the schema entirely before the datapump import. This way, datapump will re-create the schema and all of the contained objects.

Answer 3

You want to export the objects of the source database and import them into the target database. The objects of the target database that exist in the source database should be replaced by the object of the source database.

Maybe you can use the following method:

1. make a full export of the target database
2. replace the target database by a copy of the source database
3. import the dump of the original target database into the new target database

The new target database contains the objects of the source database, because they are not replaced by the import and the objects of the original target database that are not in the source database, because they are created by the import of the original target database.

There are different ways to make a copy of the source database

1. create an empty database and import the source database
2. use rman DUPLICAT command to create a copy of the source database