Results 1 to 1 of 1
  1. #1
    Join Date
    Aug 2004
    Posts
    24

    Unanswered: Extracting ddl's for multiple instances on a Server

    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.
    Last edited by madhavi_m; 09-30-04 at 12:53.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •