DBMS METADATA

DBMS_METADATA

The DBMS_METADATA package provides the ability to extract object creation DDL statements or privileged GRANT statements from the database dictionary. The following table shows the procedures and functions that make up the DBMS_METADATA package.

Procedures/Functions	Description
GET_DDL	Returns DDL statement for specified object
GET_DEPENDENT_DDL	Returns DDL statement for objects that depend on the specified object
GET_GRANTED_DDL	Returns GRANT statement for privileges granted to specified user
SET_TRANSFORM_PARAM	Whether to include specific items in the returned DDL statement
SHOW_TRANSFORM_PARAMS	Outputs the currently set transform parameter value.

GET_DDL

This returns DDL statement for specified object.

Syntax

DBMS_METADATA.GET_DDL (
   object_type     IN VARCHAR(20),
   object_name     IN VARCHAR(128),
   schema          IN VARCHAR(128) DEFAULT NULL)
RETURN VARCHAR(65534);

Parameter

Name	In/Output	Data Type	Description
object_type	IN	VARCHAR(20)	Object type
object_name	IN	VARCHAR(128)	Object name (case sensitive)
schema	IN	VARCHAR(128)	Object owner (case sensitive) If object_type is a schema object, the default value is the currently connected user; if it is a non-schema object, the default value is NULL.

object_type

Schema objects

• CONSTRAINT
• DB_LINK
• FUNCTION
• INDEX
• LIBRARY
• MATERIALIZED_VIEW
• PACKAGE
• PACKAGE_SPEC
• PACKAGE_BODY
• PROCEDURE
• QUEUE
• REF_CONSTRAINT
• SEQUENCE
• SYNONYM
• TABLE
• TRIGGER
• TYPESET
• VIEW

Non-schema objects

• DIRECTORY
• JOB
• REPLICATION
• ROLE
• TABLESPACE: Memory system tablespaces do not return DDL statements, and disk system tablespaces return ALTER statements.
• USER

Return Value

DDL Statement

Exception

invalid_argval not_supported_obj_type schema_not_found object_not_found not_supported_ddl

Example

The following example shows how to create the DDL statement for all tables owned by the connection user.

set vertical on;
SELECT TO_CHAR(dbms_metadata.get_ddl('TABLE', table_name, null)) as ddl
FROM system_.sys_tables_
WHERE table_type = 'T' AND user_id = user_id()
ORDER BY table_name;

GET_DEPENDENT_DDL

This returns DDL statement for objects that depend on the specified object.

Syntax

DBMS_METADATA.GET_DEPENDENT_DDL (
   object_type          IN VARCHAR(20),
   base_object_name     IN VARCHAR(128),
   base_object_schema   IN VARCHAR(128) DEFAULT NULL)
RETURN VARCHAR(65534);

Parameters

Name	In/Output	Data Type	Description
object_type	IN	VARCHAR(20)	Object type
base_object_name	IN	VARCHAR(128)	Base object name (case sensitive)
base_object_schema	IN	VARCHAR(128)	Base object owner (case sensitive). Default is the currently connected user.

object_type

• COMMENT
• CONSTRAINT
• INDEX
• OBJECT_GRANT
• REF_CONSTRAINT
• TRIGGER

Return Value

DDL statement

Exceptions

invalid_argval not_supported_obj_type schema_not_found object_not_found

Example

The following example shows how to get all object privileges for the T1 table of the connecting user.

set vertical on;
SELECT TO_CHAR(dbms_metadata.get_dependent_ddl('OBJECT_GRANT', 'T1')) as ddl
FROM dual;

GET_GRANTED_DDL

This returns the DDL statement for creating privileges granted to the specified user.

Syntax

DBMS_METADATA.GET_GRANTED_DDL (
   object_type          IN VARCHAR(20),
   grantee              IN VARCHAR(128) DEFAULT NULL)
RETURN VARCHAR(65534);

Parameters

Name	In/Output	Data Type	Description
object_type	IN	VARCHAR(20)	Object type
grantee	IN	VARCHAR(128)	grantee (case sensitive). Default is the current user.

object_type

• OBJECT_GRANT
• ROLE_GRANT
• SYSTEM_GRANT

Return Value

DDL statement

Exceptions

invalid_argval not_supported_obj_type grantee_not_found object_not_found

Example

This example shows how to get all system privileges granted to user USER1.

set vertical on;
SELECT TO_CHAR(dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'USER1')) as ddl
FROM dual;

SET_TRANSFORM_PARAM

Option to include specific items in the returned DDL statement. Parameter settings apply only within the same session.

Syntax

DBMS_METADATA.SET_TRANSFORM_PARAM (
   name          IN VARCHAR(40),
   value         IN CHAR(1));

Parameter

Name	In/Output	Data Type	Description
name	IN	VARCHAR(40)	Parameter name
value	IN	CHAR(1)	Value

Applied Parameters by Object Type

Object Type	Name	Description	Default
ALL	SQLTERMINATOR	Specifies whether to append an SQL terminator to the DDL statement. T: appends an SQL terminator F: does not append an SQL terminator	F
TABLE INDEX CONSTRAINT	SEGMENT_ATTRIBUTES	Specifies whether segment attributes (physical attributes, storage clause, tablespace, logging) are included. T: With F: Without	T
	STORAGE	pecifies whether or not to include. T: With F: Without	T
	TABLESPACE	Specifies whether the storage clause is included. T: With F: Without	T
TABLE	CONSTRAINTS	Specifies whether to include constraint (primary key, unique, check) except foreign key. T: With F: Without	T
	REF_CONSTRAINTS	Specifies whether or not to include a foreign key. T: With F: Without

Return Value

None

Exception

invalid_argval

Example

This example configures the SQL terminator to be appended to the returned DDL statement.

exec dbms_metadata.set_transform_param('SQLTERMINATOR', 'T');

SHOW_TRANSFORM_PARAMS

This outputs the currently set transform parameter value.

Syntax

DBMS_METADATA.SHOW_TRANSFORM_PARAMS;

Return Value

None

Exception

There is no exception.