Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    manila
    Posts
    21

    Exclamation Unanswered: Need to see the create statement of an object

    I'm a newbie with Oracle Administration and although, I can see the create statement of each object that I have using TOAD, I still wanna devise a way for me to see and generate all the create statement of my object (views, tables, triggers, procedures, etc) without looking at them one by one. I was thinking of a running a query that would extract all of the create statement but I've no idea where to start. I hope someone could help.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: Need to see the create statement of an object

    Originally posted by starra
    I'm a newbie with Oracle Administration and although, I can see the create statement of each object that I have using TOAD, I still wanna devise a way for me to see and generate all the create statement of my object (views, tables, triggers, procedures, etc) without looking at them one by one. I was thinking of a running a query that would extract all of the create statement but I've no idea where to start. I hope someone could help.
    DBMS_METADATA will do what you want to do.

  3. #3
    Join Date
    Sep 2003
    Location
    manila
    Posts
    21

    Re: Need to see the create statement of an object

    Originally posted by anacedent
    DBMS_METADATA will do what you want to do.

    Thanks for your reply. But in order to use this, i think i need to run catmeta.sql from $ORACLE_HOME/rdbms/admin. I was looking for that script but found none in our database. Could you help me get a copy?
    thanks so much

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    If you enable "Spool SQL" to clipboard/screen within TOAD you will be able to see the SQL which TOAD is issuing to get those create statements.

    It shouldn't be too difficult to expand that SQL to cover all tables/triggers etc.

    Hth
    Bill

  5. #5
    Join Date
    Sep 2003
    Posts
    6
    hi

    here you'll find a usefull script

    set feedback off
    set heading off
    set linesize 1000


    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('DBA_NULL_COL')
    order by type, line;

    hope this will help

Posting Permissions

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