How to list all table names and get the database ddl
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How to list all table names and get the database ddl

  1. #1
    Join Date
    Aug 2004
    Posts
    1

    How to list all table names and get the database ddl

    After install oracle 10g personal edition on windows XP home edition, the enterprise manager database control didn't work. I log in as super user SYSTEM, but EM database control always show error "the account is locked" and I can only startup/shutdown database, no other functions available.

    While I'm waiting for my new PC on windows XP professional edition, I try to get some ddl from the example schema. How can I list all the table names and ddl for entire database using sqlplus ????

    Thanks in advance.

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    exp. imp show=y.

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    This site has an area where sql scripts are kept.
    Perhaps one of these will work.

    You can also just look at either user_tab_column or dba_tab_columns.

    http://www.dbasupport.com/oracle/scr...e_Engineering/

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    In 9i You can use dbms_metadata pkg.
    Code:
    Example:
    select dbms_metadata.get_ddl( 'TABLE','T1','TAMIL') from dual;
    
    DBMS_METADATA.GET_DDL('TABLE','T1','TAMIL')
    -------------------------------------------------------------
      CREATE TABLE "TAMIL"."T1"
       (    "X" NUMBER(*,0),
            "X2" VARCHAR2(30),
             PRIMARY KEY ("X")
         USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
         STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 
         MAXEXTENTS 2147483645
         PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
         BUFFER_POOL DEFAULT)
         TABLESPACE "USERS"  ENABLE
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 
      MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 
      MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
      BUFFER_POOL DEFAULT)
      TABLESPACE "USERS"
    Tamil

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    I am always amazed at the amount of features that Oracle provides. I haven't yet used this package, but I will.

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by tamilselvan
    In 9i You can use dbms_metadata pkg.
    TOAD has been doing this since I started with 7.3.4 - no idea how.

Posting Permissions

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



Click Here to Expand Forum to Full Width