Re: batch generating of DDL for a set of tables

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9lrkk50o6g_at_drn.newsguy.com>...
> In article <3B81934B.5080509_at_seul.org>, marko says...
> >
> >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
> > ?
> >
>
> Lets say you want to extract the EMP table, FOO function, and EMP_IDX index --
> you could code a sql script that looks like this:
>
> ------------------------------------------------------------------------
> set long 500000
> set heading off
> set linesize 255
> column ddl format a200 word_wrapped
> spool extract.sql
>
> select dbms_metadata.get_ddl( object_type, object_name, user ) ||
> chr(10) || '/' ddl
> from user_objects
> where object_name in ( 'EMP', 'FOO', 'EMP_IDX' )
> order by decode(object_type, 'TABLE', 1, 'INDEX', 2, 'FUNCTION', 3 );
>
> spool off
> set heading on
> -----------------------------------------------------------------------
>
> When you run that:
>
> SQL> @test
>
> you'll have a file extract.sql that has in it:
>
> CREATE TABLE "SCOTT"."EMP"
> ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
> "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 "EMP_PK" PRIMARY KEY ("EMPNO")
> USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
> STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645
> PCTINCREASE 0
> FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE,
> CONSTRAINT "EMP_FK_EMP" FOREIGN KEY ("MGR")
> REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE NOVALIDATE,
> CONSTRAINT "EMP_FK_DEPT" FOREIGN KEY ("DEPTNO")
> REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE NOVALIDATE
> ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
> STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645
> PCTINCREASE 0
> FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
>
> /
>
> CREATE INDEX "SCOTT"."EMP_IDX" ON "SCOTT"."EMP" ("DEPTNO")
> PCTFREE 10 INITRANS 2 MAXTRANS 255
> STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645
> PCTINCREASE 0
> FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
>
> /
>
> CREATE OR REPLACE FUNCTION "SCOTT"."FOO" return number
> as
> begin
> return 55;
> end;
> /
>
>
> This of course works only in 9i and up which has the dbms_metadata package and
> where string concatenation to a CLOB works like that.

I am interested in finding the source for dbms_metadata. I have 9i RDBMS on linux but this package doesn't seem to exist in $ORACLE_HOME/rdbms/admin area. Can someone post or forward me the source for these package (crsoza_at_hotmail.com)?. can someone also confirm whether or not this package will work under 8i ?.

Cheers, Chris Received on Tue Aug 21 2001 - 06:53:20 CDT