Re: Export/backup Stored Database Procedures

From: GovindanK <gkatteri_at_fastmail.fm>
Date: Thu, 12 Feb 2004 17:13:54 -0800
Message-id: <402C24D2.4010608@fastmail.fm>

In addition to what Robert Freeman has suggested , here is a script which i have been using regularly (Courtesy Tom Kyte, however the wrapper we have to write ourselves).

Method I:
Tom Kyte script (say getcode.sql)

set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool &1..sql
prompt set define off
prompt set echo off
select decode( type||'-'||to_char(line,'fm99999'),

               'PACKAGE BODY-1', '/'||chr(10),
                null) ||
       decode(line,1,'create or replace ', '' ) ||
       text text

from user_source
where name = upper('&&1')
order by type, line;
prompt /
prompt set define on
prompt set echo on
spool off
set feedback on
set heading on
set termout on
set linesize 100

The wrapper script (which i wrote for my env, you can tweak it) say getcode_all.sql

REM

REM Connect to source schema and run both getcode_all.sql and getcode_all.out
REM

set heading off;
set pagesize 0
set linesize 256;
set trimspool on;
set feedback off
set verify off
spool getcode_all.out;
select '@getcode '||object_name||';'
from user_objects
where object_type IN ( 'TYPE' )
;
select '@getcode '||object_name||';'
from user_objects
where object_type IN ( 'PACKAGE' )
;
spool off
set feedback on
set verify on

Method 2: You can export with rows=n indexes=n grants=n constraints=n and then (if unix) do a

$ strings export.dmp > export.txt

At this stage use Unix shell script or perl to extract the code out of dump.
I never tried the second option myself.

HTH
GovindanK

Michael Fontana wrote:

>What methods do most on this list use to back up stored database
>procedures?
>
>I am familiar with exporting the SYS user only, and importing to another
>staging database, and pulling the source code out of there, but I must
>admit, this involves several steps and is a relative "kludge".
>
>A DBA in our group, with Sybase and SQLServer experience, complains
>because Oracle does not have a method to single out such objects for
>backup.
>
>
>
>Michael Fontana
>Sr. DBA
>NTT/Verio
>
>
>
>
>
>
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Feb 12 2004 - 19:13:54 CST