DBMS_METADATA.GET_DDL

DBMS_METADATA包中的get_ddl是获取对象定于语句的函数

–具体参数如下，次函数必须传输object_type和 name参数，其他参数有默认值可选传。

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;

以下为常用DDL查询

TABLESPACE

查询CHAICHENG表空间的定义语句

SELECT DBMS_METADATA.GET_DDL('TABLESPACE','CHAICHENG') FROM DUAL;

USER

查询CHAICHENG用户用户的定义语句

SELECT DBMS_METADATA.GET_DDL('USER','CHAICHENG') FROM DUAL;

TABLE

查询CHAICHENG用户下TEST表的定于语句

SELECT DBMS_METADATA.GET_DDL('TABLE','TEST','CHAICHENG') FROM DUAL;

VIEW

查询CHAICHENG用户下V_TEST视图的定于语句

SELECT DBMS_METADATA.GET_DDL('VIEW','V_TEST','CHAICHENG') FROM DUAL;

INDEX

查询CHAICHENG用户下TEST_INDEX01索引的定于语句

SELECT DBMS_METADATA.GET_DDL('INDEX','TEST_INDEX01','CHAICHENG') FROM DUAL;

SYNONYM

查询CHAICHENG用户下公共和私有的同义词定义语句

SELECT DBMS_METADATA.GET_DDL('SYNONYM','TEST_1','PUBLIC') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('SYNONYM','TEST_2','CHAICHENG') FROM DUAL;

SEQUENCE

查询CHAICHENG用户下SQ1序列的定义语句

SELECT DBMS_METADATA.GET_DDL('SEQUENCE,'SQ1','CHAICHENG') FROM DUAL;

PROCEDURE

查询CHAICHENG用户下存储过程P1的定义语句

SELECT DBMS_METADATA.GET_DDL('PROCEDURE','P1','CHAICHENG') FROM DUAL;

FUNCTION

查询CHAICHENG用户下存储函数F1的定义语句

SELECT DBMS_METADATA.GET_DDL('FUNCTION','F1','CHAICHENG') FROM DUAL;

TRIGGER

查询CHAICHENG用户下触发器为TG1的定义语句

SELECT DBMS_METADATA.GET_DDL('TRIGGER','TG1','CHAICHENG') FROM DUAL;

PACKAGE

查询CHAICHENG用户下PK1存储包的定义语句（包涵包壳和包体）

SELECT DBMS_METADATA.GET_DDL('PACKAGE','PK1','CHAICHENG') FROM DUAL;

SCHEDULER JOB

SELECT DBMS_METADATA.GET_DDL ( 'PROCOBJ', 'INSERT_FORBID_ITEM','NEWMESRPT') FROM DUAL;

DATABASE LINK

SELECT DBMS_METADATA.GET_DDL ( 'DB_LINK', 'DBLINK_NAME','PUBLIC') FROM DUAL;

以下为所支持的45个OBJECT TYPE