Oracle easily gets DDL statements for building tables and indexes

  • 2020-05-27 07:25:29
  • OfStack

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;  
  "ZJYW" NUMBER(10,0),     
  "SOLS" VARCHAR2(17),     
  "SORQ" VARCHAR2(8),     
  "SOWR" VARCHAR2(8),     
  "SOCL" VARCHAR2(6),     
  "YWHM" VARCHAR2(10),     
  "YWLX" VARCHAR2(6)     
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645     
  SQL> select dbms_metadata.get_ddl  
    ('INDEX','INDXX_PQZJYW','SHQSYS') from dual;    
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645     
  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;    
  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

Related articles: