Capture DDL

[lesstjm]

Is there a quick way to create a DDL script for all the objects in a tablespace. I have a bad tablespace which I want to drop and recreate so I want all the DDL. It is Oracle 9.2.0.6. I have TOAD and DBArtisian at my disposal.

Thanks.

[TimHall]

Hi.

Try this:

select dbms_metadata.get_ddl('TABLE',table_name, owner)
from dba_tables
where tablespace_name = 'USERS';

and:

select dbms_metadata.get_ddl('INDEX',index_name, owner)
from dba_indexes
where tablespace_name = 'USERS';


If the output is truncated use "SET LONG X", where X is the maximum size you need to display.

Cheers

Tim...

[padders]

Ditto. For example...

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SET LONG 100000;
SQL> SELECT DBMS_METADATA.GET_DDL (
  2           segment_type, segment_name, owner)
  3  FROM   dba_segments
  4  WHERE  tablespace_name = 'PADDERS_DATA';

  CREATE TABLE "PADDERS"."DEPT"
   (    "DEPTNO" NUMBER(2,0),
        "DNAME" VARCHAR2(14),
        "LOC" VARCHAR2(13),
         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PADDERS_DATA"  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 "PADDERS_DATA"



  CREATE TABLE "PADDERS"."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),
        "HIREORDER" NUMBER,
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PADDERS_DATA"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "PADDERS"."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 "PADDERS_DATA"



  CREATE UNIQUE INDEX "PADDERS"."PK_DEPT" ON "PADDERS"."DEPT" ("DEPTNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PADDERS_DATA"



  CREATE UNIQUE INDEX "PADDERS"."PK_EMP" ON "PADDERS"."EMP" ("EMPNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PADDERS_DATA"



4 rows selected.

SQL>

Be advised that DBMS_METADATA can be a bit slow.

[adewri]

Another way. Get DDL Wizard.

[pando]

Just wondering if anyone used DataBee before?

[adewri]

Yep I use it. It did help me during the development phase. I just take regular export with rows=n and then generate htmls for all schema using DDLWizard and deploy it on a webserver. This helps the developers to browse through the schema and see if object structures are correct or not.

[pando]

I mean DataBee not the DDL wizard!

[adewri]