Oracle9i New Features for DBA

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`.
All objects	`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`.
TYPE	`BODY`	Boolean	If T`RUE`, emit the type body. Defaults to `TRUE`.
PACKAGE	`SPECIFICATION`	Boolean	If `TRUE`, emit the package specification. Defaults to `TRUE`.
PACKAGE	`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.
All objects	`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”

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.