合 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

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。