合 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,复制内容到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')