Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

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



 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.