impdp expdp extra for experts, replace procedures triggers

 

So, you can tell import what to do if tables exists, but what about the other object types?  They all get ignored.!  note the table_exists_action.  This does not get carried over to the other object types (of course it does not).

[sourcecode language='sql'  padlinenumbers='true']
schemas=PYFINCTL,PYFINDTA
remap_schema=PYFINCTL:TRFINCTL,PYFINDTA:TRFINDTA
remap_tablespace=PYFINCTLI:TRFINCTLI,PYFINCTLT:TRFINCTLT,PYFINDTAI:TRFINDTAI,PYFINDTAT:TRFINDTAT
#table_exists_action=truncate
#table_exists_action=replace
#content=data_only
exclude=TABLE,INDEX
[/sourcecode]

I quickly had to drop all objects that were not INDEX or TABLE and re import…

[sourcecode language='sql' ]
select object_name, object_type from all_objects where owner = 'TRFINDTA' and object_type not in ('INDEX', 'TABLE') ;

select count(1), object_type from all_objects where owner = 'TRFINDTA' and object_type not in ('INDEX', 'TABLE') group by object_type;

select 'DROP ' || object_type || ' TRFINDTA.' || object_name || ';'  from all_objects where owner = 'TRFINDTA' and object_type not in ('INDEX', 'TABLE') ;
[/sourcecode]

Sometimes oracle is very very clever.  So you’ll see that I identified the objects that I needed to drop with statement 1, and then categorised them with statement 2 and found out that I might need to run about 7 different drop statements for the 7 different object types.  But!!  The miracle of oracle just allowed me to create the 1 statement, statement 3!

Then I could do the imdp again, with the exclude clause and everything was good.

Note that the above does not do triggers.

You need another parfile with include=TRIGGER and no exclude statement (they are mutually exclusive) and this will import the triggers.

What’s the lesson?  Drop everything before you impdp?