Tuesday, September 16, 2008

Get the DDL of TABLE, INDEX, VIEW, FUNCTION etc from DBMS_METADATA

The Syntax is,
dbms_metadata.get_ddl(
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

Example,
First of all, you must execute the following commnad to get the output in sql*plus,
set pagesize 0
set long 90000

If you wanna get the DDL of a table of a schema then connect to that user and,
SELECT dbms_metadata.get_ddl('TABLE', 'TEST') FROM dual;

An You can also do it from a DBA user, if the name of the schema is 'TEST1' then,
SELECT dbms_metadata.get_ddl('TABLE', 'TEST','TEST1') FROM dual;

In the same way,
SELECT dbms_metadata.get_ddl('VIEW', 'TEST') FROM dual;
SELECT dbms_metadata.get_ddl('FUNCTION', 'TEST') FROM dual;

No comments:

search engine

Custom Search