oracle取ddl的函数,[Oracle]利用DBMS_METADATA.GET_DDL函数获取数据库对象的DDL语句

如果我们想看某个表或者表空间等数据库对象的DDL语句的话，可以使用Oracle函数DBMS_METADATA.GET_DDL()函数获取，该函数有如下几个参数：

-- OBJECT_TYPE ---需要返回原数据的DDL语句的对象类型

-- NAME --- 对象名称

-- SCHEMA ---对象所在的Schema，默认为当前用户所在所Schema

-- VERSION ---对象原数据的版本

-- MODEL ---原数据的类型默认为ORACLE

-- TRANSFORM - XSL-T transform. to be applied.

-- RETURNS: 对象的原数据默认以CLOB类型返回下面以我的数据表为例，列出几个查看表、索引、约束、视图等数据库对象的DDL的SQL语句：

--查看DM_TOUCH表的DDL语句

SELECT DBMS_METADATA.GET_DDL('TABLE','DM_TOUCH') AS TABLE_DDL FROM DUAL;

--查看其它用户表的DDL语句

SELECT DBMS_METADATA.GET_DDL('TABLE','DM_TOUCH','TPIDEV') AS TABLE_DDL FROM DUAL;

--查看DM_TOUCH表的索引

SELECT T.INDEX_NAME,T.INDEX_TYPE,T.TABLE_NAME FROM USER_INDEXES T

WHERE T.TABLE_NAME='DM_TOUCH' ORDER BY INDEX_NAME;

--查看DM_TOUCH表索引的DDL

SELECT DBMS_METADATA.GET_DDL('INDEX','PK_DM_TOUCH') AS DDL_INDEX FROM DUAL;

--查看其它用户表索引的DDL

SELECT DBMS_METADATA.GET_DDL('INDEX','PK_DM_TOUCH','TPIDEV') AS DDL_INDEX FROM DUAL;

--查看DM_TOUCH表的约束

SELECT T.OWNER,T.TABLE_NAME,T.CONSTRAINT_NAME,T.CONSTRAINT_TYPE FROM USER_CONSTRAINTS T

WHERE T.TABLE_NAME='DM_TOUCH' ORDER BY CONSTRAINT_TYPE,CONSTRAINT_NAME;

--查看DM_TOUCH表约束的DDL

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','PK_DM_TOUCH') FROM DUAL;

--查看其它用户的表约束的DDL

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','PK_DM_TOUCH','TPIDEV') AS DDL_CONSTRAINT FROM DUAL;

--查看DM_TOUCH表外键的DDL语句

SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','FK_DM_TOUCH__BLUE') AS DDL_REF_CONSTRAINT FROM DUAL;

--查看用户的所有视图

SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME=UPPER('&view_name');

--查看VIEW视图的DDL

SELECT DBMS_METADATA.GET_DDL('VIEW','DM_BLUEPRINT_BALE_PLAN') AS DDL_VIEW FROM DUAL;

--查看其它用户的VIEW视图的DDL

SELECT DBMS_METADATA.GET_DDL('VIEW','DM_BLUEPRINT_BALE_PLAN','TPIDEV') AS DDL_VIEW FROM DUAL;

--DBMS_METADATA常用技巧

--1、得到一个用户下的所有表、索引、存储过程、函数的DDL语句

SELECT OBJECT_NAME,OBJECT_TYPE,DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME) AS DDL_OBJECTS FROM USER_OBJECTS U

WHERE OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE','FUNCTION');

--2、得到所有表空间的DDL语句

SELECT TABLESPACE_NAME,DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME) AS DDL_TABLESPACE FROM USER_TABLESPACES;

--3、得到所有创建用户的DDL语句

SELECT USERNAME,DBMS_METADATA.GET_DDL('USER',USERNAME) AS DDL_USER FROM USER_USERS;

--4、去除storage等多余参数

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);