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.