Re: batch generating of DDL for a set of tables

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 20 Aug 2001 08:17:57 -0700
Message-ID: <9lr9n502b24@drn.newsguy.com>

In article <3B816187.3060403_at_seul.org>, marko says...
>
>Hello,
>
>I use Oracle 9i for linux. With oemapp dbastudio I can
>generate DDL for a table and then save the sql in a file,
>but only one table at time. I cannot select multiple tables.
>
>Is it possible to generate sql file/s with DDL of a set of tables
>at once.
>
>I'd prefer to use a script for that.
>
>Thanks
>
>
>Marko
>

See dbms_metadata:

scott_at_ORA9I.WORLD> select dbms_metadata.get_ddl(object_type, object_name, user) 2 from user_objects where object_type in ( 'TABLE', 'INDEX' );

DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,USER)

CREATE TABLE "SCOTT"."BONUS"

   (    "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "SAL" NUMBER,
        "COMM" NUMBER

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCRE ASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" CREATE TABLE "SCOTT"."DEPT"

   (    "DEPTNO" NUMBER(2,0),
        "DNAME" VARCHAR2(14),
        "LOC" VARCHAR2(13),
         CONSTRAINT "DEPT_PK" PRIMARY KEY ("DEPTNO")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255   STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCRE ASE 0
  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING   STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCRE ASE 0
  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ......

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Mon Aug 20 2001 - 10:17:57 CDT