We all know that before 9i, it was a hassle to get statements to build tables and indexes. We can get it by export with rows=no, but its output is not directly available due to formatting problems. The other way is to write complex scripts to query the data dictionary, but this is still not available for slightly more complex objects such as IOT and nested tables. Getting DDL statements from a data dictionary is often used, especially during system upgrades/rebuilds. In Oracle 9i, we can directly check the DDL statement from the data dictionary by executing dbms_metadata. Using this powerful tool, we can obtain a single object or an DDL statement for the entire SCHEMA. Best of all, it’s easy to use.

1. Methods to obtain single table and index DDL statements:

　　set heading off;　　 
　　set echo off;　　 
　　Set pages 999;　　 
　　set long 90000; 
　　 
　　　 
　　 
　　spool get_single.sql　　 
　　select dbms_metadata.get_ddl('TABLE','SZT_PQSO2','SHQSYS') from dual;　　 
　　select dbms_metadata.get_ddl('INDEX','INDXX_PQZJYW','SHQSYS') from dual;　 
　　spool off;  

Here is the output. All we have to do is pull out the build/index statement and put a semicolon after it to run it.

　　SQL> select dbms_metadata.get_ddl('TABLE','SZT_PQSO2','SHQSYS') from dual; 
　　 
　　　　　 
　　CREATE TABLE "SHQSYS"."SZT_PQSO2" 　　 
　　( "PQBH" VARCHAR2(32) NOT NULL ENABLE, 　　 
　　"ZJYW" NUMBER(10,0), 　　 
　　"CGSO" NUMBER(10,0) NOT NULL ENABLE, 　　 
　　"SOLS" VARCHAR2(17), 　　 
　　"SORQ" VARCHAR2(8), 　　 
　　"SOWR" VARCHAR2(8), 　　 
　　"SOCL" VARCHAR2(6), 　　 
　　"YWHM" VARCHAR2(10), 　　 
　　"YWLX" VARCHAR2(6) 　　 
　　) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 　　 
　　STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 　　 
　　PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 　　 
　　TABLESPACE "DATA1" 　　　　 
　　SQL> select dbms_metadata.get_ddl 
    ('INDEX','INDXX_PQZJYW','SHQSYS') from dual;　　 
　　CREATE INDEX "SHQSYS"."INDXX_PQZJYW" ON "SHQSYS"."SZT_PQSO2" ("ZJYW") 　　 
　　PCTFREE 10 INITRANS 2 MAXTRANS 255 　　 
　　STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 　　 
　　PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 　　 
　　TABLESPACE "DATA1" 　　 
　　SQL> 　　 
　　SQL> spool off;  

2. Method to obtain the entire SCHEMA DDL statement:

　set pagesize 0　　 
　　set long 90000　　 
　　set feedback off　　 
　　set echo off 　　 
　　spool get_schema.sql 　　 
　　connect shqsys/shqsys@hawk1;　　 
　　SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) 
　　FROM USER_TABLES u; 
　　SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)　　 
　　FROM USER_INDEXES u;　　 
　　spool off;  

It is important to note that when we have external keys (referential constraints) in the table, we need to check the order between the referential tables to ensure that the reconstruction is carried out in a reasonable order. You can determine the order between the tables by query-dba_constraints and dba_cons_columns