How to list all table names and get the database ddl

[lilyjiang03]

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.

[Axr2]

exp. imp show=y.

[gandolf989]

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/

[tamilselvan]

In 9i You can use dbms_metadata pkg.

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

[gandolf989]

I am always amazed at the amount of features that Oracle provides. I haven't yet used this package, but I will.

[DaPi]

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.