I have a production Oracle database, and I want to export tables, data, views, materialized views, synonyms, etc, to import into a development Oracle database. Actually, this is part of my job, and we have a script in place for doing this, which calls expdp and impdp. However, I'm experiencing some issues now, because there have been a lot of recent structural changes to the production database that seem to be causing problems in conjunction with the parfile being passed to impdp. I should say that I'm quite new to expdp and impdp.
I understand about TABLE_EXISTS_ACTION and the 4 options SKIP | APPEND | TRUNCATE | REPLACE, but how can I deal with a situation where a table name exported from the source database clashes with a materialized view name in the destination database, or vice versa? What about when the name belongs to a table in one database and a synonym in the other – what does impdp do in that case, or how can/should I deal with that? Really I don't care about any existing tables, views, materialized views or synonyms in the development database; if impdp tries to import some object that was exported from the production database, I just want it to be created, and any object whose name clashes can just be dropped basically.
Also, I don't fully understand what actually happens when you export/import a materialized view from one database to another. A materialized view has a definition – so, is the actual data in the source materialized view exported, or just the definition, and the destination materialized view is refreshed according to the definition?
Best Answer
Naming conflicts will result in an error and generally cause the object in the datapump file to be skipped. This can lead to all sorts of other dependency issues. The simplest way to avoid that is to drop the target application schema/account entirely and recreate it, using a script like the one here: https://oracle-base.com/dba/script?category=script_creation&file=user_ddl.sql to construct the right user DDL for the user in the target database, or by including the application schema definition in the datapump file and letting datapump create it for you. So...
Step 1: run the script to get the create user DDL.
Step 2: drop the user using the cascade option to drop all objects.
Step 3: run the user DDL script from step 1 to recreate the schema, assuming the password is different or there are any other differences between production and development that you need to preserve.
Step 4: run impdp to load all of the schema objects. If datapump tries to create the schema and it already exists it will throw an error, but you can ignore it.
For your second question, materialized views have two parts: a table with data, and the materialized view definition, which includes the query. Both - the table with data and the definition of the MV - are replicated with datapump when you copy a schema.