Generate table DDL tips Oracle Tips by Burleson Consulting Don Burleson

In the early days when the Oracle database was much less robust and therefore much simpler, DBAs often wrote SQL scripts to generate the database objects? DDL code. These scripts simply queried that data dictionary and produced human readable SQL files of what the database design reality was at that particular time. The old_generate_table_ddl.sql SQL*Plus script, shown below, is a simple example of what such a script might have looked like.

 

set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 132
 
define schema=&1
define CR=chr(10)
define TAB=chr(9)
col x noprint
col y noprint
select  table_name y,
        0 x,
        'CREATE TABLE ' ||
        rtrim(table_name) ||
        '('
from    dba_tables
where   owner = upper('&schema')
union
select  tc.table_name y,
        column_id x,
        decode(column_id,1,'    ','   ,')||
        rtrim(column_name)|| &TAB || &TAB ||
        rtrim(data_type) ||
        rtrim(decode(data_type,'DATE',null,'LONG',null,
               'NUMBER',decode(to_char(data_precision),null,null,'('),
               '(')) ||
        rtrim(decode(data_type,
               'DATE',null,
               'CHAR',data_length,
               'VARCHAR2',data_length,
               'NUMBER',decode(to_char(data_precision),null,null,
                 to_char(data_precision) || ',' || to_char(data_scale)),
               'LONG',null,
               '******ERROR')) ||
        rtrim(decode(data_type,'DATE',null,'LONG',null,
               'NUMBER',decode(to_char(data_precision),null,null,')'),
               ')')) || &TAB || &TAB ||
        rtrim(decode(nullable,'N','NOT NULL',null))
from    dba_tab_columns tc,
        dba_objects o
where   o.owner = tc.owner
and     o.object_name = tc.table_name
and     o.object_type = 'TABLE'
and     o.owner = upper('&schema')
union
select  table_name y,
        999999 x,
        ')'  || &CR
        ||'  STORAGE('                                   || &CR
        ||'  INITIAL '    || initial_extent              || &CR
        ||'  NEXT '       || next_extent                 || &CR
        ||'  MINEXTENTS ' || min_extents                 || &CR
        ||'  MAXEXTENTS ' || max_extents                 || &CR
        ||'  PCTINCREASE '|| pct_increase                || ')' ||&CR
        ||'  INITRANS '   || ini_trans                   || &CR
        ||'  MAXTRANS '   || max_trans                   || &CR
        ||'  PCTFREE '    || pct_free                    || &CR
        ||'  PCTUSED '    || pct_used                    || &CR
        ||'  PARALLEL (DEGREE ' || rtrim(DEGREE) || ') ' || &CR
        ||'  TABLESPACE ' || rtrim(tablespace_name)      ||&CR
        ||'/'||&CR||&CR
from    dba_tables
where   owner = upper('&schema')
order by 1,2;

This is not too bad. But with the plethora of table structural design options such as clustering, partitioning, index organized tables, external tables and such, it is clear that this little script would need thousands of lines of code plus more of the same for indexes and views.

 Generate DDL with dbms_metadata

Prior to Oracle, getting table and index DDL was a time-consuming and tricky process.  You could run the export utility with ROWS=NO, but the output was hard to re-use because of quoted strings.  The only other option was to write complex dictionary scripts that might not work on complex objects such as IOT and nested tables.

Punching DDL from the dictionary is very useful when you are migrating a system to a new platform and you want to pre-create the objects in a new tablespace so that you can import with IGNORE=Y.

In Oraclewe have the exciting new dbms_metadata utility to display DDL directly from the data dictionary.  Using this powerful utility, we can punch individual objects or an entire schema.

Best of all, it is easy.  You simply execute dbms_metadata. get_ddl.

To punch off all table and indexes for the EMP table, we execute dbms_metadata. get_ddl, select from DUAL, and providing all required parameters.

 

set heading off;

set echo off;

Set pages 999;

set long 90000;

 

spool ddl_list.sql

 

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

 

select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;

 

spool off;

Here is the output.  The only thing missing is the ending semicolons after each statement.  Just for illustration, we show how a primary key can be punched as part of the table DDL or separately using the INDEX argument.

CREATE TABLE "SCOTT"."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

  STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50

  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"  ENABLE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING

  STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50

  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"

 

 

  CREATE UNIQUE INDEX "SCOTT"."DEPT_IDX" ON "SCOTT"."DEPT" ("DNAME")

  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"

Now we can modify the syntax to punch a whole schema.  It us easily done by selecting dbms_metadata. get_ddl and specifying USER_TABLES and USER_INDEXES. :

set pagesize 0
set long 90000
set feedback off
set echo off 

spool scott_schema.sql 

connect scott/tiger;

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;

So examine the exact same table generation process instead using the Oracle dbms_metadata PL/SQL package as shown in this SQL*Plus display table DDL script:

set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 132
define schema=&1

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
SELECT to_char(DBMS_METADATA.GET_DDL ('TABLE', table_name, owner))
FROM dba_tables
WHERE owner=upper('&1');

In just 12 short lines, a script is created to reverse engineer all the tables for a given schema and for every possible Oracle option or feature those tables use. Furthermore, now the task of keeping such a script current is now on Oracle?s shoulders. So even if Oracle adds new table options or parameters like extends or changes to the CREATE/ALTER table syntax, the script is not affected. Additionally, this DDL generation script can be extended to change or add additional objects types because it is very straightforward and easy. For example, if one wanted to switch to or add indexes, just substitute or add this command.

SELECT to_char(DBMS_METADATA.GET_DDL ('INDEX', index_name, table_owner))
FROM dba_indexes
WHERE table_owner=upper('&1');

Now compare the actual table CREATE TABLE DDL generated from the new_generate_table_ddl.sql SQL*Plus script, shown next, to the earlier output from the old_generate_table_ddl.sql SQL*Plus script. Note that check constraints, primary keys and unique keys have been picked up along with their storage clauses. Furthermore, even the table storage clause is more accurate with items such as NOCOMPRESS, NOLOGGING and BUFFER_POOL now covered.

You can use expdp to export only the table, index and constraint definitions for a table or a group of tables.  Here is the export syntax to only extract the table definitions using the metadata_only argument:

$expdp scott/tiger full=y content=METADATA_ONLY exclude=STATISTICS

---