合 Oracle中如何导出存储过程、函数、包和触发器的定义语句？如何导出表的结构？如何导出索引的创建语句？
Tags： Oracle
QQ群里有人问：如何导出一个用户下的存储过程？
麦苗答：方法有多种，可以使用DBMS_METADATA.GET_DDL包。
- 使用PL/SQL DEVELOPER工具
-- 下面的SQL语句，如果报错：ORA-22835: 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小 (实际: 4994, 最大: 4000)，那么去掉TO_CAHR
SELECT TO_CHAR(DBMS_METADATA.GET_DDL('PROCEDURE', U.OBJECT_NAME)) ||CHR(10)||'/'
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'PROCEDURE'
;
然后将结果拷贝到Excel中，
打开Excel，复制内容到plsql developer里边，注意粘贴的时候使用右键的“Past from host Language”，否则粘贴后的代码含有双引号：
运行这些脚本脚本即可：
- 使用SQL*Plus
使用如下的脚本即可导出某个用户下的存储过程代码到/tmp/a.sql文件中：
SET PAGESIZE 0
SET TRIMSPOOL ON
SET LINESIZE 10000
SET LONG 90000
SET FEEDBACK OFF
SET FEED OFF;
SET ECHO OFF
spool /tmp/a.sql
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', U.OBJECT_NAME)||CHR(10)||'/'
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'PROCEDURE';
spool OFF
打开文件后，简单处理一下即可。
总体来说有两种方式来获取，第一，利用系统包DBMS_METADATA包中的GET_DDL函数来获取，第二，利用exp或expdp来获取。
下面来看第一种方式，如何利用系统包DBMS_METADATA包中的GET_DDL函数来获取对象的定义语句。下面是该函数的入参和出参：
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
其详细参数如下：
l OBJECT_TYPE 需要返回原数据的DDL语句的对象类型
l NAME 对象名称
l SCHEMA 对象所在的SCHEMA，默认为当前用户所在所SCHEMA
l VERSION 对象原数据的版本
l MODEL 原数据的类型默认为ORACLE
l TRANSFORM 默认值为DDL
l RETURNS 对象的原数据默认以CLOB类型返回
一般情况下，只需要给出OBJECT_TYPE、NAME和SCHEMA3个参数即可。
n 查看创建表SQL语句：
SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME) FROM USER_TABLES U;
n 查看创建索引的SQL语句：
SELECT DBMS_METADATA.GET_DDL('INDEX','PK_DEPT','SCOTT') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME) FROM USER_INDEXES U;
n 查看创建主键的SQL语句：
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','EMP_PK') FROM DUAL;
n 查看创建外键的SQL语句：
SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','EMP_FK_DEPT') FROM DUAL;
n 查看创建视图（VIEW）的SQL语句：
SELECT DBMS_METADATA.GET_DDL('VIEW', 'MY_TABLES','SCOTT') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('VIEW', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'VIEW';
SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME=UPPER('&VIEW_NAME');
n 查看创建存储过程（PROCEDURE）的SQL语句：
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'PROCEDURE';
n 查看创建触发器（TRIGGER）的SQL语句：
SELECT DBMS_METADATA.GET_DDL('TRIGGER', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'TRIGGER';
n 查看创建函数（FUNCTION）的SQL语句：
SELECT DBMS_METADATA.GET_DDL('FUNCTION', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'FUNCTION';
n 查看创建包（PACKAGE）的SQL语句：
SELECT DBMS_METADATA.GET_DDL('PACKAGE', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'PACKAGE';
n 查看创建序列（SEQUENCE）的SQL语句：
SELECT DBMS_METADATA.GET_DDL('SEQUENCE', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'SEQUENCE';
n 查看创建同义词（SYNONYM）的SQL语句：
SELECT DBMS_METADATA.GET_DDL('SYNONYM', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'SYNONYM';
n 查看创建表空间（TABLESPACE）的SQL语句：
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', U.TABLESPACE_NAME)
FROM USER_TABLESPACES U;
n 查看创建角色（ROLE）的SQL语句：
SELECT DBMS_METADATA.GET_DDL('ROLE', U.ROLE) FROM DBA_ROLES U;
n 查看创建用户（USER）的SQL语句：
SELECT DBMS_METADATA.GET_DDL('USER','SYS') FROM DUAL;
n 得到某个SCHEDULER JOB的创建语句：
SELECT DBMS_METADATA.GET_DDL('PROCOBJ', D.JOB_NAME, D.OWNER)
FROM DBA_SCHEDULER_JOBS D
WHERE D.JOB_TYPE = 'STORED_PROCEDURE'
AND D.STATE = 'SCHEDULED'
AND D.SCHEDULE_NAME IS NULL;
n 得到一个用户下的所有表、索引、存储过程、函数的DDL语句：
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE U.OBJECT_TYPE IN ('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION');
如果想去掉表的存储参数（例如，INITIAL、NEXT、FREELISTS等参数），那么可以使用DBMS_METADATA包中的函数SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE)来完成，代码如下所示：
SYS@lhrdb> SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SYS@lhrdb> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
PL/SQL procedure successfully completed.
SYS@lhrdb> SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
使用DBMS_METADATA.GET_DDL需要注意以下问题：
（1）DBMS_METADATA.GET_DDL()包内的参数都要大写，否则会报ORA-31600: invalid input value table for parameter OBJECT_TYPE in function GET_DDL的错误。
（2）是否查的当前用户的DDL语句，若不是则需要加上对象的属主信息即SCHEMA参数。
（3）若在SQL*Plus中显示不全，则需要set long 9999。
（4）对于DBMS_METADATA.GET_DDL包，可以在PLSQL Developer工具中运行，也可以在SQL*Plus中运行。
如果要导出SCOTT用户下的所有定义，那么在SQL*Plus中代码如下所示：
SET PAGESIZE 0
SET TRIMSPOOL ON
SET LINESIZE 10000
SET LONG 90000
SET FEEDBACK OFF
SET FEED OFF;
SET ECHO OFF
SPOOL /tmp/schema_scott.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
('TABLE', 'INDEX', 'PROCEDURE', 'FUNCTION' ， 'PACKAGE', 'TRIGGER')
