Weekly mail magazine dedicated to Oracle users, E-Magazine

Always documentation is priority one, but what I like with these kind of resources is that they support the beginners with simple examples and they are free accessible :)

http://www.insight-tec.com/en/mailmagazine/index.html

Also today with a question of one of my colleague I searched and learned about dbms_utility.get_dependency function, I love this package :)

CREATE TABLE testtab (testcol VARCHAR2(20));

CREATE VIEW testview AS SELECT * FROM testtab;

CREATE TRIGGER testtrig
BEFORE INSERT
ON testtab
BEGIN
  NULL;
END testtrig;
/

CREATE OR REPLACE PROCEDURE testproc IS
 i PLS_INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO i
  FROM testtab;

  dbms_output.put_line(TO_CHAR(i));
END testproc;
/

set serveroutput on
exec dbms_utility.get_dependency('TABLE', 'HR', 'TESTTAB');

DEPENDENCIES ON HR.TESTTAB
------------------------------------------------------------------
*TABLE HR.TESTTAB()
*   VIEW HR.TESTVIEW()
*   TRIGGER HR.TESTTRIG()
*   PROCEDURE HR.TESTPROC()

PL/SQL procedure successfully completed

And the last words are for this interesting presentation which I was warned about by one of my colleagues, Block Level Tuning by Rich Niemiec, I guess you will also enjoy it as much as I did – http://www.nyoug.org/Presentations/2005/20050929tuningoracle.pdf

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.