Oracle9i New Features - Get Object DDL Statements with DBMS_Metadata Packages
Author: eygle
Source: http://blog.eygle.com
Date: decEmber 22, 2004
«Oracle's difficulties in Renke | Blog Home
From Oracle9i, Oracle provides a new system package DBMS_Metadata, which can be used to extract DDL statements created by objects.
This package function is extremely powerful, let's take a look at its use.
1. Get the creation statement of the table.
SQL> DESC DBMS_METADATA
Function Add_Transform Returns Number
...
Function Get_DDL Returns Clob
Argument Name Type In / Out Default?
------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------- --------
Object_type varchar2 in
Name varchar2 in
Schema varchar2 in default
Version varchar2 in Default
Model varchar2 in Default
Transform varchar2 in Default
....
SQL> SET Long 2000
SQL> SELECT DBMS_METADATA.GET_DDL ('Table', 'Test') from Dual;
DBMS_METADATA.GET_DDL ('Table', 'Test')
-------------------------------------------------- ------------------------------
Create Table "SYS". "Test"
("Owner" varchar2 (30),
"Object_name" varchar2 (128),
"SubObject_name" varchar2 (30),
"Object_id" Number,
"Data_Object_ID" NUMBER,
"Object_type" varchar2 (18),
"Created" Date,
"Last_DDL_Time" Date,
"TimeStamp" VARCHAR2 (19),
"Status" varchar2 (7),
"Temporary" VARCHAR2 (1),
"Generated" varchar2 (1),
"Secondary" varchar2 (1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS Logging
Storage (Initial 65536 Next 1048576 Mineltnts 1 maxExtents 2147483645pctincrease 0 free) 1 FreeList Groups 1 buffer_pool default)
TableSpace "System"
SQL>
2. Further, you can get a more comprehensive DDL statement via DBMS_METADATA
Oracle provides a comprehensive example: $ oracle_home / rdbms / demo / mddemo.sql includes indexing, authorization, all DDL statements, and more, all DDL statements, and more can be extracted.
Interested in referring to the file, tested in accordance with DEMO.
