Extracting ddl's for multiple instances on a Server

madhavi_m · #1 (**permalink**) 09-30-04, 11:16

I have the following code, which will extract the ddl's on a separate .sql files on a file server at one location for one database instance.

1) Create the script called getObject.sql:
Code:

HTML Code:

spool &1..sql
select dbms_metadata.get_ddl('&3','&1','&2' ) from dual;
spool off;

Then, Ran this Code:
2)

HTML Code:

set pagesize 0 verify off feedback off

Spool bigscript.sql

SELECT '@getobject ' || OBJECT_NAME || ' ' || OWNER || ' ' || OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION','TRIGGER',’PACKAGE’) AND OWNER NOT IN (‘SYS’,’SYSTEM’);

Spool off;

3)

HTML Code:

@bigscript

Now, I want to have databasename in that spool file name to differentiate for multiple database instances.

I have to extract the files on the server at some location,

HTML Code:

\oracle\source\instance1.proc1.sql, instance1.proc2.sql.....
\oracle\source\instance2.proc1.sql, instance2.proc2.sql....

How can I achieve this?

I would really appreciate your help.

Refer to the link: http://www.dbforums.com/showthread.p...35#post3774835 for previous posts.

Thanks, Madhavi.