DM dbms_metadata.get_ddl方法的使用总结

DM dbms_metadata.get_ddl方法的使用总结

DBMS_METADATA包GET_DDL函数用于获取数据库对表、视图、索引、全文索引、存储过程、函数、包、序列、同义词、约束、触发器等的DDL语句。

可以通过DM管理工具轻松获得所需对象的SQL定义语句，在一些无法使用DM管理工具的地方，可以使用DM的disql工具获得对象的SQL定义语句。以下内容侧重于在DMdisql工具中获得对象的SQL定义语句。

特别提示MPP环境下不支持使用DBMS_METADATA包。

1、DBMS_METADATA包GET_DDL方法介绍

获取指定对象元数据中的DDL语句。

1.1 语法定义

FUNCTION GET_DDL(

OBJECT_TYPE IN VARCHAR(30),

NAME IN VARCHAR(128),

SCHNAME   

1.2 参数详解

l OBJECT_TYPE

对象类型。包括表、视图、物化视图、索引、全文索引、存储过程、函数、包、目录等，详情请见OPEN参数详解。其中，OBJECT_TYPE只能为大写。

OBJECT_TYPE的类型包括：

类型名称	含义	说明
CLASS	类类型	默认返回类头类体
CLASS_HEAD	类型名	无
CLASS_BODY	类型体	无
COL_STATISTICS	列统计	无
COMMENT	注释	无
CONSTRAINT	约束	不包括聚集主键和非空约束
CONTEXT	上下文	无
CONTEXT_INDEX	全文索引	无
DATABASE_EXPORT	数据库下的所有对象	库级导出
DB_LINK	数据库链接	因此类对象具有所有者,因此将其视为模式级对象。 对于公有连接，它们的所有者是PUBLIC；对于私有链接，它们的创建者就是它们的所有者
DIRECTORY	目录	无
DOMAIN	域	无
FUNCTION	存储函数	无
INDEX	索引	不包括系统内部定义的索引
INDEX_STATISTICS	索引统计	无
JOB	任务	无
OBJECT_GRANT	对象权限	无
PACKAGE	包	默认返回包头包体
PKG_SPEC	包头	无
PKG_BODY	包体	无
POLICY	策略	无
PROCEDURE	存储过程	无
ROLE	角色	无
ROLE_GRANT	角色权限	无
SCHEMA_EXPORT	模式下的所有对象	模式级导出
SEQUENCE	序列	无
SYNONYM	同义词	私有同义词为模式对象，公有同义词为命名对象
SYSTEM_GRANT	系统权限	无
TABLE	表	无
TABLE_STATISTICS	表统计信息	无
TABLE_EXPORT	表及与其相关的元数据	表级导出
TABLESPACE	表空间	无
TRIGGER	触发器	无
USER	用户	无
VIEW	视图	无
TYPE	用户自定义类型	无
MATERIALIZED_VIEW	物化视图	无
MATERIALIZED_VIEW_LOG	物化视图日志	无

l NAME

对象名称，区分大小写。

l SCHEMA

模式，默认是当前用户模式。

1.3 返回值

以DDL返回对象元数据中的DDL语句。

错误处理

INVALID_ARGVAL：如果输入参数中存在空值或非法值。

OBJECT_NOT_FOUND：如果指定的对象在数据库中不存在。

1.4 注意

（1）如果使用disql需要进行下列格式化，特别需要对long进行设置，否则无法显示完整的SQL

（2）参数要使用大写，否则会查不到

set linesize 180

set pages 999

set long 1000

2、查看数据库表的SQL定义语句

--查看BAOBIAO用户下A_20170630QFMX1表的定义语句

select dbms_metadata.get_ddl('TABLE', 'A_20170630QFMX1', 'BAOBIAO') from dual;

3、查看创建视图的SQL定义语句

--查看SYSDBA用户下的视图V_EMP的定义语句

SELECT DBMS_METADATA.GET_DDL('VIEW','V_EMP','SYSDBA') FROM DUAL;

4、查看索引的SQL定义语句

--查询BAOBIAO用户下索引INDEX33555571的定义语句

select dbms_metadata.get_ddl('INDEX','INDEX33555571','BAOBIAO') from dual;

5、查看全文索引的SQL定义语句

--查询SYSDBA用户下全文索引CONTEXT_ADDR_ADDR的定义语句

select dbms_metadata.get_ddl('CONTEXT_INDEX','CONTEXT_ADDR_ADDR','SYSDBA') from dual;

6、获取存储过程的SQL定义语句

--查询SYSDBA用户下存储过程SP_DB_BAKSET_REMOVE_BATCH的定义语句

select DBMS_METADATA.GET_DDL('PROCEDURE','SP_DB_BAKSET_REMOVE_BATCH','SYSDBA') from dual

SQL> select DBMS_METADATA.GET_DDL('PROCEDURE','SP_DB_BAKSET_REMOVE_BATCH','SYSDBA') from dual;

LINEID  DBMS_METADATA.GET_DDL('PROCEDURE','SP_DB_BAKSET_REMOVE_BATCH','SYSDBA')                                                                                          

CREATE OR REPLACE PROCEDURE SP_DB_BAKSET_REMOVE_BATCH (    DEVICE_TYPE VARCHAR,    TIME               DATETIME) AS BEGIN   SF_BAKSET_REMOVE_BATCH(DEVICE_TYPE, TIME, 1, NULL);END;

used time: 3.039(ms). Execute id is 1601.

SQL>

7、获取用户下函数的SQL定义语句

--查询BAOBIAO用户下函数FCHKSPACE的定义语句

select DBMS_METADATA.GET_DDL('FUNCTION','FCHKSPACE','BAOBIAO') from DUAL;

8、获取包的SQL定义语句

--查询BAOBIAO用户下包PG_REPORT的定义语句

select DBMS_METADATA.GET_DDL('PACKAGE','PG_REPORT','BAOBIAO') from dual；

9、获取用户下序列的SQL定义语句

--查询BAOBIAO用户下序列REQNO的定义语句

select DBMS_METADATA.GET_DDL('SEQUENCE','REQNO','BAOBIAO') from DUAL;

10、获取用户下同义词的SQL定义语句

--查询SYSDBA用户下同义词EMP的定义语句

select DBMS_METADATA.GET_DDL('SYNONYM','EMP','SYSDBA') from DUAL;

--查询BAOBIAO用户下同义词DEP的定义语句

select DBMS_METADATA.GET_DDL('SYNONYM','DEP','BAOBIAO') from DUAL;

11、获取用户下约束的SQL定义语句

--查询SALES用户下约束CONS134218815的定义语句

select DBMS_METADATA.GET_DDL('CONSTRAINT ','CONS134218815','SALES') from DUAL;

ALTER TABLE "SALES"."SALESORDER_HEADER" ADD CONSTRAINT  CHECK(STATUS IN (0, 1, 2, 3, 4, 5));

12、获取用户下的触发器的SQL定义语句

select DBMS_METADATA.GET_DDL('TRIGGER','TRIGGERNAME','USERNAME) FROM DUAL;

select * from ALL_TRIGGERS；

--查询DMHR用户下触发器DEL_TRG的定义语句

select DBMS_METADATA.GET_DDL('TRIGGER','DEL_TRG','DMHR') FROM DUAL;

14、获取物化视图的SQL定义语句

select dbms_metadata.get_ddl('MATERIALIZED VIEW','MVNAME') FROM DUAL;

--查询物化视图MV_EMP的定义语句

select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MV_EMP') FROM DUAL;