Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

December 26, 2007

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

Filed under: Oracle How To — H.Tonguç Yılmaz @ 12:30 pm

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.

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: