Oracle9i new features-using DBMS

oracle| Object | Statement oracle9i new attribute-Get Object DDL statement using Dbms_metadata Package Author: eygle Source: http://blog.eygle.com Date: December 22, 2004
«oracle integration of the difficulties of the Benevolence division | Blog Home

Starting with Oracle9i Oracle provides a new system package dbms_metadata that can be used to extract DDL statements from object creation.

This package function is extremely powerful, let's look at how it is used.

1. Get the creation statement for the table.

sql> desc dbms_metadatafunction add_transform RETURNS number ... FUNCTION get_ddl RETURNS CLOB Argument Name Type in/out Default? -------------------------------------------------------------------object_type VARCHAR2 in NAME VARCHAR2 in SCHEMA VA RCHAR2 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 pctused Initrans 1 Maxtrans 255 nocompress LOGGING
STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645
Pctincrease 0 freelists 1 freelist GROUPS 1 buffer_pool DEFAULT)
Tablespace "SYSTEM"

Sql>







2. Further, more comprehensive DDL statements can be obtained through dbms_metadata

Oracle provides a comprehensive example:
$ORACLE _home/rdbms/demo/mddemo.sql
All DDL statements, including indexes, authorizations, triggers, and so on, can be extracted.

Interested can refer to the document, follow the example of demo test.