Re: batch generating of DDL for a set of tables

From: marko mikulicic <marko_at_seul.org>
Date: Mon, 20 Aug 2001 18:46:35 -0400
Message-ID: <3B81934B.5080509@seul.org>

Thomas Kyte wrote:
> 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)
This seems to me the cleanest way. I have tried various perl scripts but they missed informations about constrains and keys.

Just a couple of questions if someone has patience with me:

the get_dll function returns a clob. How can I dump it to a file from within sqlplus (or procedure) (no perl please, I want to learn sql!) ?
is there a similar way to dump plsql procedures and functions ?

Thanks

Marko

> --------------------------------------------------------------------------------
> 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 - 17:46:35 CDT