oracle生成ddl语句,Oracle导出用户ddl语句 存储过程、触发器、函数、包、表、索引...

1. 生成sql脚本，SQL plus执行，类型名称、用户名需大写

SET PAGESIZE 0

SET TRIMSPOOL ON

SET LINESIZE 10000

SET LONG 90000

SET FEEDBACK OFF

SET FEED OFF

SET ECHO OFF

-- 指定文件路径，文件夹不能为空

SPOOL E:\ytzz\copy\czbdc_dp\schema_bdc_workflow.sql

SELECT CASE

WHEN U.OBJECT_TYPE IN (‘PROCEDURE‘, ‘FUNCTION‘ ， ‘PACKAGE‘, ‘TRIGGER‘) THEN

DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME, U.OWNER) ||

CHR(10) || ‘/‘

ELSE

DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME, U.OWNER)||

CHR(10) || ‘;‘

END AS SCOTT_DDL

FROM DBA_OBJECTS U

WHERE U.OBJECT_TYPE IN (‘PROCEDURE‘, ‘FUNCTION‘, ‘PACKAGE‘, ‘TRIGGER‘)

AND U.OWNER=‘TEST‘;

SPOOL OFF;

2. 查询

-- 根据用户对象名和对象类型，查找对面内容 存储过程、函数、触发器 -- PROCEDURE TRIGGER FUNCTION PACKAGE

SELECT DBMS_METADATA.GET_DDL(‘PROCEDURE‘, U.OBJECT_NAME,‘BDCK‘)

-- 任意用户登录都可以

from dba_objects U

where u.OBJECT_TYPE in (‘PROCEDURE‘) and u.owner = ‘BDCK‘

;

--查触发器内容 -- PROCEDURE TRIGGER FUNCTION

SELECT DBMS_METADATA.GET_DDL(‘PROCEDURE‘, U.OBJECT_NAME)

-- 当前登录用户

FROM USER_OBJECTS U

WHERE OBJECT_TYPE = ‘FUNCTION‘

;

函数使用参数说明

SQL> DESC DBMS_METADATA.GET_DDL

PARAMETER TYPE MODE DEFAULT?

----------- -------- ---- --------

(RESULT) CLOB

OBJECT_TYPE VARCHAR2 IN

NAME VARCHAR2 IN

SCHEMA VARCHAR2 IN Y

VERSION VARCHAR2 IN Y

MODEL VARCHAR2 IN Y

TRANSFORM VARCHAR2 IN Y

补充 把clob转换为字符串

SELECT dbms_lob.substr(DBMS_METADATA.GET_DDL(‘FUNCTION‘, ‘FUNC_NAME‘, ‘SCHEMA‘)) as 内容 FROM dual;

原文：https://www.cnblogs.com/shihx/p/13509737.html