Re: show create table in oracle?

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 29 Jan 2004 21:35:28 -0800
Message-ID: <1a75df45.0401292135.55eb69c@posting.google.com>

"feo_at_soy.madre" <paslical 2000-QUITAESTO_at_yahoo.es> wrote :
>
> I need to recover table definition from a database in oracle, how can i
> obtain sql sentences what generated the table?.
> in mysql the comand is "show create table people", but i don t know in
> oracle.

This works fine in Oracle 9i. Not sure if the metadata package existed in 8i.

SQL> select table_name from user_tables;

TABLE_NAME

---

BONUS
DEPT
EMP
FOO
SALGRADE

SQL> set pages 0
SQL> set long 999999
SQL> select

  2 dbms_metadata.get_ddl( 'TABLE', 'EMP', 'SCOTT' )   3 from dual;

  CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)   TABLESPACE "USERS" ENABLE,

         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)   TABLESPACE "USERS"

--
Billy

Received on Thu Jan 29 2004 - 23:35:28 CST