合 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')
AND U.OWNER='SCOTT';
SPOOL OFF;
则可以导出SCOTT用户下所有的DDL语句到/tmp/schema_scott.sql文件中。
如果在PLSQL Developer工具中运行，那么可以单独运行如下的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')
AND U.OWNER='SCOTT';
然后选择整列，右键选择“Copy to Excel”，就可以将数据导出到Excel文件中，接着，将Excel中的数据复制到PLSQL Developer工具的“SQL Window”中皆可。需要注意的是，最后复制到“SQL Window”中的时候，需要选择右键的“Past from host Language”，否则粘贴的代码含有双引号，需要做特殊处理，比较麻烦。
可以使用如下的SQL脚本生成某个用户下的所有对象的DDL语句：
sqlplus<<eof <="" eof
set long 100000
set head off
set echo off
set pagesize 0
set verify off
set feedback off
spool schema.out
select dbms_metadata.get_ddl(object_type, object_name, owner)
from
(
--Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
select
owner,
--Java object names may need to be converted with DBMS_JAVA.LONGNAME.
--That code is not included since many database don't have Java installed.
object_name,
decode(object_type,
'DATABASE LINK', 'DB_LINK',
'JOB', 'PROCOBJ',
'RULE SET', 'PROCOBJ',
'RULE', 'PROCOBJ',
'EVALUATION CONTEXT', 'PROCOBJ',
'PACKAGE', 'PACKAGE_SPEC',
'PACKAGE BODY', 'PACKAGE_BODY',
'TYPE', 'TYPE_SPEC',
'TYPE BODY', 'TYPE_BODY',
'MATERIALIZED VIEW', 'MATERIALIZED_VIEW',
'QUEUE', 'AQ_QUEUE',
'JAVA CLASS', 'JAVA_CLASS',
'JAVA TYPE', 'JAVA_TYPE',
'JAVA SOURCE', 'JAVA_SOURCE',
'JAVA RESOURCE', 'JAVA_RESOURCE',
object_type
) object_type
from dba_objects
where owner in ('LHR')
--These objects are included with other object types.
and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',
'LOB','LOB PARTITION','LOB SUBPARTITION','TABLE PARTITION','TABLE SUBPARTITION','PROGRAM')
--Ignore system-generated types that support collection processing.
and not (object_type = 'TYPE' and object_name like 'SYSPLSQL%')
--Exclude nested tables, their DDL is part of their parent table.
and (owner, object_name) not in (select owner, table_name from dba_nested_tables)
--Exlclude overflow segments, their DDL is part of their parent table.
and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')
)
order by owner, object_type, object_name;
spool off
quit
EOF
cat schema.out|sed 's/OWNER1/MYOWNER/g'>schema.out.change.sql
下面介绍第二种导出元数据的方法，就是采用exp或expdp命令。数据泵工具（impdp）提供了SQLFILE的命令行选项，只获取DDL语句，并未真正地执行数据导入。另外，若单纯为了导出DDL语句则可以在使用expdp导出的时候使用CONTENT=METADATA_ONLY和EXCLUDE=STATISTICS选项，这样导出的DMP文件比较小。如下所示：
expdp \'/ AS SYSDBA\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=lhrsql20161215.dmp LOGFILE=lhrsql20161215.log CONTENT=METADATA_ONLY SCHEMAS=SCOTT EXCLUDE=STATISTICS
impdp \'/ AS SYSDBA\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=lhrsql20161215.dmp LOGFILE=imp_exptest.log SQLFILE=expddl_lhr.sql
查看expddl_lhr.sql文件即可获取DDL语句。整个示例如下所示：
ZFZHLHRDB1:oracle:/oracle>expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=exptest_sql.dmp logfile=exp_exptest.dmp
Export: Release 11.2.0.4.0 - Production on Wed Aug 3 15:14:55 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/**** AS SYSDBA" directory=DATA_PUMP_DIR schemas=SCOTT dumpfile=exptest_sql.dmp logfile=exp_exptest.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."TEST" 5.007 KB 1 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/oracle/app/oracle/admin/lhrdb/dpdump/exptest_sql.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Aug 3 15:15:16 2016 elapsed 0 00:00:20
ZFZHLHRDB1:oracle:/oracle>impdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp logfile=imp_exptest.log sqlfile=exptest.sql
Import: Release 11.2.0.4.0 - Production on Wed Aug 3 15:16:06 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01": "/**** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=exptest_sql.dmp logfile=imp_exptest.log sqlfile=exptest.sql
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Wed Aug 3 15:16:09 2016 elapsed 0 00:00:02
ZFZHLHRDB1:oracle:/oracle>cd /oracle/app/oracle/admin/lhrdb/dpdump/
ZFZHLHRDB1:oracle:/oracle/app/oracle/admin/lhrdb/dpdump>more exptest.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:268AB71B15071D81F19C6FC5041FA8F8E49397470FFE05458B8C90D9E7F8;F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PASSWORD EXPIRE
ACCOUNT LOCK;
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "SCOTT";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
GRANT "CONNECT" TO "SCOTT";
GRANT "RESOURCE" TO "SCOTT";
-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "SCOTT" DEFAULT ROLE ALL;
-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
-- CONNECT SCOTT
BEGIN
