oracle导出metadata,获得ORACLE DDL 通过DBMS_METADATA

set pagesize 0

set long 90000

set feedback off

set echo off

--数据库登录

conn test/test@orcl--导出文件名称spool oracleObj.sql--输出信息采用缩排或换行格式化EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'PRETTY', TRUE);--确保每个语句都带分号EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SQLTERMINATOR', TRUE);--关闭表索引、外键等关联(后面单独生成)EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'CONSTRAINTS', FALSE);EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'REF_CONSTRAINTS', FALSE);EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', FALSE);--关闭存储、表空间属性EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'STORAGE', FALSE);EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'TABLESPACE', FALSE);--关闭创建表的PCTFREE、NOCOMPRESS等属性EXEC DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);--创建用户对象SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)FROM USER_OBJECTS uwhere U.OBJECT_TYPE IN ('TABLE','VIEW','SYNONYM','SEQUENCE');SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)FROM USER_OBJECTS uwhere U.OBJECT_TYPE IN ('INDEX','TRIGGER');SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)FROM USER_OBJECTS uwhere U.OBJECT_TYPE IN ('FUNCTION');SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)FROM USER_OBJECTS uwhere U.OBJECT_TYPE IN ('PROCEDURE');--获取表注释SELECT DBMS_LOB.substr(DBMS_METADATA.get_dependent_ddl ('COMMENT', table_name))FROM (SELECT distinct table_nameFROM user_col_commentsWHERE comments IS NOT NULL);spool off;