DBMS_METADATA

Oracle9i New Feature Series: Extract DDL from database using DBMS_METADATA

DBMS_METADATA is a powerful package provided in Oracle9i to extract the object definitions from the database. The following are the programs available in the package:

Subprogram

Description

OPEN Procedure

Specifies the type of object to be retrieved, the version of its metadata, and the object model.

SET_FILTER Procedure

Specifies restrictions on the objects to be retrieved, for example, the object name or schema.

SET_COUNT Procedure

Specifies the maximum number of objects to be retrieved in a single FETCH_xxx call.

GET_QUERY Procedure

Returns the text of the queries that are used by FETCH_xxx.

SET_PARSE_ITEM Procedure

Enables output parsing by specifying an object attribute to be parsed and returned.

ADD_TRANSFORM Procedure

Specifies a transform that FETCH_xxx applies to the XML representation of the retrieved objects.

SET_TRANSFORM_PARAM Procedure

Specifies parameters to the XSLT stylesheet identified by transform_handle.

FETCH_xxx Procedure

Returns metadata for objects meeting the criteria established by OPEN, SET_FILTER, SET_COUNT, ADD_TRANSFORM, and so on.

CLOSE Procedure

Invalidates the handle returned by OPEN and cleans up the associated state.

GET_XML and GET_DDL Functions

Returns the metadata for the specified object as XML or DDL.

GET_DEPENDENT_XML and GET_DEPENDENT_DDL Functions

Returns the metadata for one or more dependent objects, specified as XML or DDL.

GET_GRANTED_XML and GET_GRANTED_DDL Functions

Returns the metadata for one or more granted objects, specified as XML or DDL.

Here is an example:

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE',false);
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','SALES_HISTORY') FROM DUAL;
 
DBMS_METADATA.GET_DDL('TABLE','SALES_HISTORY')
-------------------------------------------------------
 
CREATE TABLE "MYUSER"."SALES_HISTORY"
( "YEAR" NUMBER(4,0),
"JAN" NUMBER,
 
1 row selected.
 
SQL> set long 32000
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','SALES_HISTORY') FROM DUAL;
 
DBMS_METADATA.GET_DDL('TABLE','SALES_HISTORY')
------------------------------------------------------------------
 
CREATE TABLE "MYUSER"."SALES_HISTORY"
( "YEAR" NUMBER(4,0),
"JAN" NUMBER,
"FEB" NUMBER,
"MAR" NUMBER,
"APR" NUMBER,
"MAY" NUMBER,
"JUN" NUMBER,
"JUL" NUMBER,
"AUG" NUMBER,
"SEP" NUMBER,
"OCT" NUMBER,
"NOV" NUMBER,
"DEC" NUMBER,
"REGION" CHAR(2)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS"
 
1 row selected.
 
SQL>
SET_TRANSFORM_PARAM: Transform Parameters for the DDL Transform

Object Type

Name

Datatype

Meaning

All objects

PRETTY

Boolean

If TRUE, format the output with indentation and line feeds. Defaults to TRUE.

SQLTERMINATOR

Boolean

If TRUE, append a SQL terminator (; or /) to each DDL statement. Defaults to FALSE.

TABLE

SEGMENT_ATTRIBUTES

Boolean

If TRUE, emit segment attributes (physical attributes, storage attributes, tablespace, logging). Defaults to TRUE.

STORAGE

Boolean

If TRUE, emit storage clause. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.

TABLESPACE

Boolean

If TRUE, emit tablespace. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.

TABLE

CONSTRAINTS

Boolean

If TRUE, emit all non-referential table constraints. Defaults to TRUE.

REF_CONSTRAINTS

Boolean

If TRUE, emit all referential constraints (foreign key and scoped refs). Defaults to TRUE.

CONSTRAINTS_AS_ALTER

Boolean

If TRUE, emit table constraints as separate ALTER TABLE (and, if necessary, CREATE INDEX) statements. If FALSE, specify table constraints as part of the CREATE TABLE statement. Defaults to FALSE. Requires that CONSTRAINTS be TRUE.

OID

Boolean

If TRUE, emit the OID clause for object tables. Defaults to FALSE.

SIZE_BYTE_KEYWORD

Boolean

If TRUE, emit the BYTE keyword as part of the size specification of CHAR and VARCHAR2 columns that use byte semantics. If FALSE, omit the keyword. Defaults to FALSE.

INDEX

SEGMENT_ATTRIBUTES

Boolean

If TRUE, emit segment attributes (physical attributes, storage attributes, tablespace, logging). Defaults to TRUE.

STORAGE

Boolean

If TRUE, emit storage clause. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.

TABLESPACE

Boolean

If TRUE, emit tablespace. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.

TYPE

SPECIFICATION

Boolean

If TRUE, emit the type specification. Defaults to TRUE.

BODY

Boolean

If TRUE, emit the type body. Defaults to TRUE.

PACKAGE 

SPECIFICATION

Boolean

If TRUE, emit the package specification. Defaults to TRUE.

BODY

Boolean

If TRUE, emit the package body. Defaults to TRUE.

VIEW

FORCE

Boolea

If TRUE, use the FORCE keyword in the CREATE VIEW statement. Defaults to TRUE.

All objects

DEFAULT

Boolean

Calling SET_TRANSFORM_PARAM with this parameter set to TRUE has the effect of resetting all parameters for the transform to their default values. Setting this FALSE has no effect. There is no default.

INHERIT

Boolean

If TRUE, inherits session-level parameters. Defaults to FALSE. If an application calls ADD_TRANSFORM to add the DDL transform, then by default the only transform parameters that apply are those explicitly set for that transform handle. This has no effect if the transform handle is the session transform handle.

The two tables on this page are excerpt from Oracle documentation PL/SQL Supplied Packages

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.