What is the easiest(or best) way to export and deploy to another Oracle 10gR2 instance only the views,triggers,procedures,packages and functions?

One of my friends, with nick name FENERBAHCE at OTN forums, asked on the easiest way to export and deploy to another Oracle only the views,triggers,procedures,packages and functions; http://forums.oracle.com/forums/thread.jspa?threadID=599890

His purpose is briefly to deploy a schema’s “create or replace” type of Oracle objects from development to test on 10g Release 2. And two options were discussed on the thread dbms_metadata and expdp/impdp with 10g. Below steps I tried and suggested for his purpose using expdp/impdp;

1- $ mkdir /tmp/dp

2- $ sqlplus dest_user/dest_passwd

SQL> create or replace directory ext_tab_dir as '/tmp/dp';

SQL> create database link dev_dblink connect to dev_user identified by dev_passwd using 'dev_tns' ;

SQL> select * from global_name@dev_dblink ;

3- $ expdp dest_user/dest_passwd NETWORK_LINK=dev_dblink DIRECTORY=ext_tab_dir EXCLUDE=USER,TABLESPACE_QUOTA,JAVA_SOURCE,JAVA_CLASS,TABLE,INDEX,SEQUENCE,TYPE,DB_LINK DUMPFILE=dev_export.dmp LOGFILE=dev_export.log

4- $ impdp dest_user/dest_passwd DIRECTORY=ext_tab_dir DUMPFILE=dev_export.dmp LOGFILE=test_import.log SQLFILE=ext_tab_dir:dev_export_ddl.sql

5- $ vi /tmp/dp/dev_export_ddl.sql

To find and replace correctly you may use this one in the sql ddl file produced by impdp;

:1,$s/CREATE PROCEDURE /CREATE or replace PROCEDURE /g
:1,$s/CREATE FORCE VIEW /CREATE or replace VIEW /g
:1,$s/CREATE FUNCTION /CREATE or replace FUNCTION /g
:1,$s/CREATE PACKAGE BODY /CREATE or replace PACKAGE BODY /g
:1,$s/CREATE PACKAGE /CREATE or replace PACKAGE /g

Since with expdp when you exclude table, triggers are also excluded below steps are also needed for just the triggers.

6- $ expdp dest_user/dest_passwd NETWORK_LINK=dev_dblink DIRECTORY=ext_tab_dir INCLUDE=TRIGGER DUMPFILE=dev_export_trigger.dmp LOGFILE=dev_export_trigger.log

7- $ impdp dest_user/dest_passwd DIRECTORY=ext_tab_dir DUMPFILE=dev_export_trigger.dmp LOGFILE=test_import_trigger.log SQLFILE=ext_tab_dir:dev_export_trigger_ddl.sql

8- vi /tmp/dp/dev_export_trigger_ddl.sql

:1,$s/CREATE TRIGGER /CREATE or replace TRIGGER /g

9- $ sqlplus dest_user/dest_passwd 

spool /tmp/dp/dev_export_ddl_sqlplus.log
@/tmp/dp/dev_export_ddl.sql
spool off

spool /tmp/dp/dev_export_trigger_ddl_sqlplus.log
@/tmp/dp/dev_export_trigger_ddl.sql
spool off

It is best to compile all invalid sources as a last step.

10- $ sqlplus dest_user/dest_passwd 

spool /tmp/dp/utlrp.log
@?/rdbms/admin/utlrp.sql
spool off

Some additional hints are;

a. to see briefly what object types are inside ddl file you can search in the with “/– new object type path is” in vi editor,
b. to be careful with “&” signed comments inside your codes during running the ddl sql file, or search and replace them with “:1,$s/&/ and /g” in vi editor,

Finally as I already mentioned on the OTN thread I really suspect that this one is the easiest or best method to deploy all and only the sql and pl/sql based database objects, so I thought to blog about it to catch some comments and share them.

Advertisement