Prior to Oracle9i, 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 Oracle9i we 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;
Syntax
challenge: When you have
foreign keys (“references” constraints), it would be nice to punch the
table DDL in their proper order for re-loading into another database.
If you do not order the table, a table may be added that references
another table that has not yet been added. Making this change
would involve adding a WHERE clause that queries
dba_constraints and dba_cons_columns to determine the proper
table order.
Get the Advanced Oracle Monitoring
and Tuning Script Collection
The complete collection with 681 working Oracle scripts
- Including 10g RAC & Grid scripts
- Certified for 11g
You can download
them immediately at this link:
http://www.rampant-books.com/download_adv_mon_tuning.htm
