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 u
where U.OBJECT_TYPE IN ('TABLE','VIEW','SYNONYM','SEQUENCE')
;
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('INDEX','TRIGGER')
;
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('FUNCTION')
;
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('PROCEDURE')
;
--获取表注释
SELECT DBMS_LOB.substr(DBMS_METADATA.get_dependent_ddl ('COMMENT', table_name))
FROM (SELECT distinct table_name
FROM user_col_comments
WHERE comments IS NOT NULL
)
;
spool off;
[转载]获得ORACLE DDL 通过DBMS_METADATA
最新推荐文章于 2022-01-01 00:43:59 发布