概述

虽然我们用PLSQL可以看到数据库对象的DDL语句，但如果想要用sql来实现呢？下面先讲下DBMS_METADATA的作用。

DBMS_METADATA

The DBMS_METADATA package provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.

GET_xxx Functions

The following GET_xxx functions let you fetch metadata for objects with a single call:

• GET_XML
• GET_DDL
• GET_SXML
• GET_DEPENDENT_XML
• GET_DEPENDENT_DDL
• GET_GRANTED_XML
• GET_GRANTED_DDL

获得表、索引、视图、存储过程、函数的DDL

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual;

select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual;

select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual;

select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','PROCEDURE_OWNER') from dual;

select dbms_metadata.get_ddl('FUNCTION','FUNCTION_NAME','FUNCTION_OWNER') from dual;

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~