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