--------------------------------------------------------------------------------------------------------------
- --TroubleShooting :对于DBMS_METADATA API的正确使用方式
- --Description :DBMS_METADATA常用功能介绍、如何重建数据库失效对象、附赠部分问题处理脚本
- --Author :600团队
- --Remark :The article was produced by 600 teams
- --TimeStamp :2018-07-22 12:30
--------------------------------------------------------------------------------------------------------------
1.基于Oracle官方对DBMS_METADATA做如下分解介绍：
The DBMS_METADATA package provides a way for you to retrieve metadata from the database dictionary as XML or
creation DDL and to submit the XML to re-create the object.
DBMS_METADATA包为我们提供了一种方法，可以从数据库字典中检索作为XML或创建DDL的元数据，并提交XML以重新创建对象
DBMS_METADATA概念及其功能介绍：
用于DDL检索的API和用于DDL和关联的API
•起源于9i版本，到了Oracle 10g得到增强，随着版本迭代更新12c功能更加强大
•支撑Oracle 10g 11g datapump
•默认情况下返回XML
•过滤器(排除某些对象或过滤器过滤对象类型)
•转换(例如，返回SQL DDL转换(例如，返回SQL DDL)
•解析项(以返回有关解析项的信息)
--------------------------------------------------------------------------------------------------------------
2.代码演示：
--获取表的元数据
SELECT DBMS_METADATA.GET_DDL('TABLE', U.TABLE_NAME)
FROM USER_ALL_TABLES U
WHERE U.NESTED = 'NO'
AND (U.IOT_TYPE IS NULL OR U.IOT_TYPE = 'IOT');
注：如果该表是一个索引组织的表，那么IOT_TYPE就是IOT、IOT_OVERFLOW或IOT_MAPPING。如果该表不是索引组织的表，
则IOT_TYPE为NULL
NESTED指示该表是否是嵌套表(YES)或否(NO)
--GET_DEPENDENT_DDL GET_DEPENDENT_DDL获取约束信息
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT', 'EMPLOYEES', 'HR')
FROM DUAL;
Answer: GRANT SELECT ON "HR"."EMPLOYEES" TO "SCOTT" WITH GRANT OPTION
GRANT REFERENCES ON "HR"."EMPLOYEES" TO "OE"
GRANT SELECT ON "HR"."EMPLOYEES" TO "OE"
--获取某个用户系统权限
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', '&OWNER') FROM DUAL;
Answer: GRANT MANAGE SCHEDULER TO "SCOTT"
GRANT ON COMMIT REFRESH TO "SCOTT"
GRANT GLOBAL QUERY REWRITE TO "SCOTT"
GRANT QUERY REWRITE TO "SCOTT"
GRANT CREATE MATERIALIZED VIEW TO "SCOTT"
GRANT CREATE ANY PROCEDURE TO "SCOTT"
GRANT CREATE ANY INDEX TO "SCOTT"
GRANT DELETE ANY TABLE TO "SCOTT"
GRANT SELECT ANY TABLE TO "SCOTT"
GRANT UNLIMITED TABLESPACE TO "SCOTT"
GRANT ALTER SYSTEM TO "SCOTT"
--该函数比较两个对象的元数据，并返回一组ALTER语句，以便像object2那样创建对象1
SELECT DBMS_METADATA_DIFF.COMPARE_ALTER(OBJECT_TYPE => '&OBJECT_TYPE',
NAME1 => '&NAME1',
NAME2 => '&NAME2',
SCHEMA1 => '&OWNER1',
SCHEMA2 => '&OWNER2')
FROM DUAL;
Answer:ALTER TABLE "SCOTT"."T_600_COMPARE1" DROP ("COL_ENAME")
ALTER TABLE "SCOTT"."T_600_COMPARE1" DROP ("COL_DEPTNO")
ALTER TABLE "SCOTT"."T_600_COMPARE1" RENAME TO "T_600_COMPARE2"
--获取约束和权限：
BEGIN
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform,
'SQLTERMINATOR', true);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform,
'SEGMENT_ATTRIBUTES', false);
END;
/
SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME)
FROM USER_TABLES
WHERE TABLE_NAME IN ('DEPT', 'EMP');
/
BEGIN
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,
'CONSTRAINTS_AS_ALTER',
TRUE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,
'REF_CONSTRAINTS',
FALSE);
END;
/
SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME)
FROM USER_TABLES
WHERE TABLE_NAME IN ('DEPT', 'EMP');
/
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', TABLE_NAME)
FROM USER_TABLES T
WHERE TABLE_NAME IN ('EMP', 'DEPT')
AND EXISTS (SELECT 1
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = T.TABLE_NAME
AND CONSTRAINT_TYPE = 'R');
注：SQLTERMINATOR为true是为了在每个命令的末尾获得分号
SEGMENT_ATTRIBUTES为false是为了缩短生成的ddl脚本时间
用ALTER语句通过将CONSTRAINTS_AS_ALTER参数设置为true来分别创建约束
通过将REF_CONSTRAINTS设置为false，在生成DDL期间跳过引用约束
--------------------------------------------------------------------------------------------------------------
3.了解DBMS_METADATA的data types，然后我们来做一个综合案例：
--返回所有DDL的函数的代码片段分解介绍，以下为代码片段1：
3.1代码片段1：
create or replace function func_get_ddl_for_table(p_schema_name in varchar2,
p_sqlterminator in boolean default false)
--该sys.ku$_ddls为系统定义类型返回值
return sys.ku$_ddls
is
v_outputddls sys.ku$_ddls := sys.ku$_ddls(); <----通过变量接收系统定义的类型
v_parsed_items parsed_items_t;
begin
v_handle := dbms_metadata.open(‘TABLE’);<----打开一个上下文句柄
--设置筛选器，只获取指定模式的对象
dbms_metadata.set_filter(v_handle,cv_schema, p_schema_name);
注：DBMS_METADATA API基于对象和表类型，要么在类型中定义，要么在SYS系统类型中定义
常用的两个系统定义类型为 KU$_PARSED ITEM 和KU$_DDL，它们以数组的形式呈现，例如如下代码
CREATE TYPE
sys.ku$_parsed_item
AS OBJECT (
item VARCHAR2(30),
value VARCHAR2(4000),
object_row NUMBER )
/
CREATE TYPE
sys.ku$_ddl
AS OBJECT (
ddlText CLOB,
parsedItem
sys.ku$_parsed_items )
--------------------------------------------------------------------------------------------------------------
4.综合实例演示：
CREATE OR REPLACE FUNCTION FUNC_TABLEDDL(P_INPUT_VALUES SYS_REFCURSOR)
--pipe管道接收
RETURN T_600_TABLEDDL_TYPE
PIPELINED IS
--设定自治事务
PRAGMA AUTONOMOUS_TRANSACTION;
--定义变量
--创建T_600_TABLEDDL对象类型表的字段信息用于变量接收，例如table_name通过V_TABLE_NAME接收
V_TABLE_NAME VARCHAR2(30);
--创建T_600_TABLEDDL对象类型表的字段信息用于变量接收，例如orig_schema通过V_orig_schema接收
V_ORIG_SCHEMA VARCHAR2(30);
--创建T_600_TABLEDDL对象类型表的字段信息用于变量接收，例如comp_schema通过V_comp_schema接收
V_COMP_SCHEMA VARCHAR2(30);
-- 设置T_600_TABLEDDL对象类型表输出记录
OUT_REC T_600_TABLEDDL := T_600_TABLEDDL(NULL, NULL, NULL, NULL, NULL);
--获取元数据信息句柄为了跟踪不同的对象DDL,在function中我们来具体引用
HOPENORIG0 NUMBER;
HOPENORIG NUMBER;
HOPENCOMP NUMBER;
HMODIFYORIG NUMBER;
HTRANSDDL NUMBER;
DMSF PLS_INTEGER; --dbms_metadata.session_transform接收变量
***********************************
*clob持有DDL
*Orig_ddl0将保存要比较的对象的基线DDL
*Orig_ddl1还将保存要比较的对象的基线DDL但是在被比较之前也会经过校正
*针对Comp_ddl2 Comp_ddl2将包含要与基线进行比较的DDL
*************************************/
ORIG_DDL0 CLOB;
ORIG_DDL1 CLOB;
COMP_DDL2 CLOB;
RET NUMBER;
BEGIN
--去掉DDL中不涉及的属性。如果你关心表空间、存储或段信息只注释掉这几行
DMSF := DBMS_METADATA.SESSION_TRANSFORM;
DBMS_METADATA.SET_TRANSFORM_PARAM(DMSF, 'TABLESPACE', FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DMSF, 'STORAGE', FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DMSF, 'SEGMENT_ATTRIBUTES', FALSE);
-- 通过游标循环遍历结果集
LOOP
--传入参数通过游标变量接收
FETCH P_INPUT_VALUES
INTO V_TABLE_NAME, V_ORIG_SCHEMA, V_COMP_SCHEMA;
EXIT WHEN P_INPUT_VALUES%NOTFOUND;
--获取原始对象类型标的DDL定义元数据
HOPENORIG0 := DBMS_METADATA.OPEN('TABLE');
DBMS_METADATA.SET_FILTER(HOPENORIG0, 'NAME', 'T_600_TABLEDDL_TYPE');
DBMS_METADATA.SET_FILTER(HOPENORIG0, 'SCHEMA', 'SCOTT');
* 对将进行转换的原始表DDL重新设置句柄，我们为原始对象DDL设置了两个句柄，因为我们希望能够。
操作一个集进行比较，但将原始DDL打印输出 */
HOPENORIG := DBMS_METADATA.OPEN('TABLE');
DBMS_METADATA.SET_FILTER(HOPENORIG, 'NAME', 'T_600_TABLEDDL_TYPE');
DBMS_METADATA.SET_FILTER(HOPENORIG, 'SCHEMA', 'SCOTT');
-- 设置表的句柄，以便与原始表进行比较
HOPENCOMP := DBMS_METADATA.OPEN('TABLE');
DBMS_METADATA.SET_FILTER(HOPENCOMP, 'NAME', 'T_600_TABLEDDL_TYPE');
DBMS_METADATA.SET_FILTER(HOPENCOMP, 'SCHEMA', 'SCOTT');
--修改“orig_schema”的转换，使其具有comp_schema权限
HMODIFYORIG := DBMS_METADATA.ADD_TRANSFORM(HOPENORIG, 'MODIFY');
DBMS_METADATA.SET_REMAP_PARAM(HMODIFYORIG,
'REMAP_SCHEMA',
'SCOTT',
'SCOTT');
-- 创建DDL，但不比较XML
HTRANSDDL := DBMS_METADATA.ADD_TRANSFORM(HOPENORIG0, 'DDL');
HTRANSDDL := DBMS_METADATA.ADD_TRANSFORM(HOPENORIG, 'DDL');
HTRANSDDL := DBMS_METADATA.ADD_TRANSFORM(HOPENCOMP, 'DDL');
-- 获取DDD并将其存储到CLOB PL/SQL变量中
ORIG_DDL0 := DBMS_METADATA.FETCH_CLOB(HOPENORIG0);
ORIG_DDL1 := DBMS_METADATA.FETCH_CLOB(HOPENORIG);
--把基线对象存储于Comp_schema中
BEGIN
COMP_DDL2 := DBMS_METADATA.FETCH_CLOB(HOPENCOMP);
EXCEPTION
WHEN OTHERS THEN
COMP_DDL2 := 'DOES NOT EXIST';
END;
-- 比较两个DDL语句和输出行
RET := DBMS_LOB.COMPARE(ORIG_DDL1, COMP_DDL2);
IF RET != 0 THEN
OUT_REC.TABLE_NAME := 'T_600_TABLEDDL_TYPE';
OUT_REC.ORIG_SCHEMA := 'SCOTT';
OUT_REC.ORIG_DDL := ORIG_DDL0;
OUT_REC.COMP_SCHEMA := 'SCOTT';
OUT_REC.COMP_DDL := COMP_DDL2;
PIPE ROW(OUT_REC);
END IF;
-- 关闭游标并释放句柄
DBMS_METADATA.CLOSE(HOPENORIG0);
DBMS_METADATA.CLOSE(HOPENORIG);
DBMS_METADATA.CLOSE(HOPENCOMP);
END LOOP;
RETURN;
END FUNC_TABLEDDL;
SELECT *
FROM TABLE(FUNC_TABLEDDL(CURSOR (SELECT TABLE_NAME, OWNER, 'SCOTT'
FROM DBA_TABLES
WHERE OWNER = 'SCOTT')));
注：为了代码演示方便，我们用对象类型表代替函数中的变量，保证编译通过，实际使用过程可以将TABLE_NAME直接替换
或者当传入参数利用ref cursor直接代入即可。
--------------------------------------------------------------------------------------------------------------
5.对于dbms_metadata API简单封装代码，方便查询元数据内容
CREATE OR REPLACE PACKAGE PKG_GET_INFO IS
*************************************************************
* Cpmtemt:Use This Package For Get Database Object Information
* AUTHOR :600团队
* TimeStamp :2016-04-22 15:27:00
*************************************************************/
--------------------------------------------------------------
--Get Procedure Script
--------------------------------------------------------------
PROCEDURE PROC_GET_PROCEDURE_INFO;
--------------------------------------------------------------
--Get Function Script
--------------------------------------------------------------
PROCEDURE PROC_GET_FUNCTION_INFO;
--------------------------------------------------------------
--Get Trigger Script
--------------------------------------------------------------
PROCEDURE PROC_GET_TRIGGER_INFO;
--------------------------------------------------------------
--Get View Script
--------------------------------------------------------------
PROCEDURE PROC_GET_VIEW_INFO;
--------------------------------------------------------------
--Get Sequence Script
--------------------------------------------------------------
PROCEDURE PROC_GET_SEQUENCE_INFO;
--------------------------------------------------------------
--Get Table Script
--------------------------------------------------------------
PROCEDURE PROC_GET_TABLE_INFO;
END PKG_GET_INFO;
/
CREATE OR REPLACE PACKAGE BODY PKG_GET_INFO IS
-----------------------------------------------
--Global Contant
C_PROCEDURE CONSTANT VARCHAR2(20) := 'PROCEDURE';
C_FUNCTION CONSTANT VARCHAR2(20) := 'FUNCTION';
C_TRIGGER CONSTANT VARCHAR2(20) := 'TRIGGER';
C_VIEW CONSTANT VARCHAR2(20) := 'VIEW';
C_SEQUENCE CONSTANT VARCHAR2(20) :='SEQUENCE';
C_USER CONSTANT VARCHAR2(20) := 'SCOTT';
C_TABLE CONSTANT VARCHAR2(20) :='TABLE';
-----------------------------------------------
-----------------------------------------------
--Get Procedure Script
-----------------------------------------------
PROCEDURE PROC_GET_PROCEDURE_INFO IS
V_BEGIN_TIME NUMBER;
V_END_TIME NUMBER;
V_RESULT NUMBER;
L_ERR_NUMS PLS_INTEGER;
V_PROCEDURE SYS.DBA_OBJECTS.OBJECT_NAME%TYPE;
V_CLOB CLOB;
CURSOR CUR_PROCEDURE IS
SELECT OBJECT_NAME
FROM DBA_OBJECTS
WHERE OWNER = Q'[INVEST]'
AND OBJECT_TYPE = Q'[PROCEDURE]';
BEGIN
DBMS_OUTPUT.ENABLE(BUFFER_SIZE => NULL);
SELECT DBMS_UTILITY.GET_TIME INTO V_BEGIN_TIME FROM DUAL;
OPEN CUR_PROCEDURE;
LOOP
FETCH CUR_PROCEDURE
INTO V_PROCEDURE;
SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE => C_PROCEDURE,
NAME => V_PROCEDURE,
SCHEMA => C_USER)
INTO V_CLOB
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_CLOB);
EXIT WHEN CUR_PROCEDURE%NOTFOUND;
END LOOP;
SELECT DBMS_UTILITY.GET_TIME INTO V_END_TIME FROM DUAL;
SELECT TO_CHAR(V_END_TIME - V_BEGIN_TIME) 100
INTO V_RESULT
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
L_ERR_NUMS := SQLCODE;
DBMS_OUTPUT.PUT_LINE(L_ERR_NUMS || '-------' || SQLERRM);
END PROC_GET_PROCEDURE_INFO;
-----------------------------------------------
--Get Function Script
-----------------------------------------------
PROCEDURE PROC_GET_FUNCTION_INFO IS
V_BEGIN_TIME NUMBER;
V_END_TIME NUMBER;
V_RESULT NUMBER;
L_ERR_NUMS PLS_INTEGER;
V_FUNCTION SYS.DBA_OBJECTS.OBJECT_NAME%TYPE;
V_CLOB CLOB;
CURSOR CUR_PROCEDURE IS
SELECT OBJECT_NAME
FROM DBA_OBJECTS
WHERE OWNER = Q'[INVEST]'
AND OBJECT_TYPE = Q'[FUNCTION]';
BEGIN
DBMS_OUTPUT.ENABLE(BUFFER_SIZE => NULL);
SELECT DBMS_UTILITY.GET_TIME INTO V_BEGIN_TIME FROM DUAL;
OPEN CUR_PROCEDURE;
LOOP
FETCH CUR_PROCEDURE
INTO V_FUNCTION;
SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE => C_FUNCTION,
NAME => V_FUNCTION,
SCHEMA => C_USER)
INTO V_CLOB
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_CLOB);
EXIT WHEN CUR_PROCEDURE%NOTFOUND;
END LOOP;
SELECT DBMS_UTILITY.GET_TIME INTO V_END_TIME FROM DUAL;
SELECT TO_CHAR(V_END_TIME - V_BEGIN_TIME) 100
INTO V_RESULT
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
L_ERR_NUMS := SQLCODE;
DBMS_OUTPUT.PUT_LINE(L_ERR_NUMS || '-------' || SQLERRM);
END PROC_GET_FUNCTION_INFO;
-----------------------------------------------
--Get Trigger Script
-----------------------------------------------
PROCEDURE PROC_GET_TRIGGER_INFO IS
V_BEGIN_TIME NUMBER;
V_END_TIME NUMBER;
V_RESULT NUMBER;
L_ERR_NUMS PLS_INTEGER;
V_TRIGGER SYS.DBA_OBJECTS.OBJECT_NAME%TYPE;
V_CLOB CLOB;
CURSOR CUR_PROCEDURE IS
SELECT OBJECT_NAME
FROM DBA_OBJECTS
WHERE OWNER = Q'[INVEST]'
AND OBJECT_TYPE = Q'[TRIGGER]';
BEGIN
DBMS_OUTPUT.ENABLE(BUFFER_SIZE => NULL);
SELECT DBMS_UTILITY.GET_TIME INTO V_BEGIN_TIME FROM DUAL;
OPEN CUR_PROCEDURE;
LOOP
FETCH CUR_PROCEDURE
INTO V_TRIGGER;
SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE => C_TRIGGER,
NAME => V_TRIGGER,
SCHEMA => C_USER)
INTO V_CLOB
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_CLOB);
EXIT WHEN CUR_PROCEDURE%NOTFOUND;
END LOOP;
SELECT DBMS_UTILITY.GET_TIME INTO V_END_TIME FROM DUAL;
SELECT TO_CHAR(V_END_TIME - V_BEGIN_TIME) 100
INTO V_RESULT
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
L_ERR_NUMS := SQLCODE;
DBMS_OUTPUT.PUT_LINE(L_ERR_NUMS || '-------' || SQLERRM);
END PROC_GET_TRIGGER_INFO;
-----------------------------------------------
--Get View Script
-----------------------------------------------
PROCEDURE PROC_GET_VIEW_INFO IS
V_BEGIN_TIME NUMBER;
V_END_TIME NUMBER;
V_RESULT NUMBER;
L_ERR_NUMS PLS_INTEGER;
V_VIEW SYS.DBA_OBJECTS.OBJECT_NAME%TYPE;
V_CLOB CLOB;
CURSOR CUR_PROCEDURE IS
SELECT OBJECT_NAME
FROM DBA_OBJECTS
WHERE OWNER = Q'[INVEST]'
AND OBJECT_TYPE = Q'[VIEW]';
BEGIN
DBMS_OUTPUT.ENABLE(BUFFER_SIZE => NULL);
SELECT DBMS_UTILITY.GET_TIME INTO V_BEGIN_TIME FROM DUAL;
OPEN CUR_PROCEDURE;
LOOP
FETCH CUR_PROCEDURE
INTO V_VIEW;
SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE => C_VIEW,
NAME => V_VIEW,
SCHEMA => C_USER)
INTO V_CLOB
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_CLOB||';');
EXIT WHEN CUR_PROCEDURE%NOTFOUND;
END LOOP;
SELECT DBMS_UTILITY.GET_TIME INTO V_END_TIME FROM DUAL;
SELECT TO_CHAR(V_END_TIME - V_BEGIN_TIME) 100
INTO V_RESULT
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
L_ERR_NUMS := SQLCODE;
DBMS_OUTPUT.PUT_LINE(L_ERR_NUMS || '-------' || SQLERRM);
END PROC_GET_VIEW_INFO;
-----------------------------------------------
--Get View Script
-----------------------------------------------
PROCEDURE PROC_GET_SEQUENCE_INFO IS
V_BEGIN_TIME NUMBER;
V_END_TIME NUMBER;
V_RESULT NUMBER;
L_ERR_NUMS PLS_INTEGER;
V_SEQUENCE SYS.DBA_OBJECTS.OBJECT_NAME%TYPE;
V_CLOB CLOB;
CURSOR CUR_PROCEDURE IS
SELECT OBJECT_NAME
FROM DBA_OBJECTS
WHERE OWNER = Q'[INVEST]'
AND OBJECT_TYPE = Q'[SEQUENCE]';
BEGIN
DBMS_OUTPUT.ENABLE(BUFFER_SIZE => NULL);
SELECT DBMS_UTILITY.GET_TIME INTO V_BEGIN_TIME FROM DUAL;
OPEN CUR_PROCEDURE;
LOOP
FETCH CUR_PROCEDURE
INTO V_SEQUENCE;
SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE => C_SEQUENCE,
NAME => V_SEQUENCE,
SCHEMA => C_USER)
INTO V_CLOB
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_CLOB||';');
EXIT WHEN CUR_PROCEDURE%NOTFOUND;
END LOOP;
SELECT DBMS_UTILITY.GET_TIME INTO V_END_TIME FROM DUAL;
SELECT TO_CHAR(V_END_TIME - V_BEGIN_TIME) 100
INTO V_RESULT
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
L_ERR_NUMS := SQLCODE;
DBMS_OUTPUT.PUT_LINE(L_ERR_NUMS || '-------' || SQLERRM);
END PROC_GET_SEQUENCE_INFO;
-----------------------------------------------
--Get Table Script
-----------------------------------------------
PROCEDURE PROC_GET_TABLE_INFO IS
V_BEGIN_TIME NUMBER;
V_END_TIME NUMBER;
V_RESULT NUMBER;
L_ERR_NUMS PLS_INTEGER;
V_TABLE SYS.DBA_OBJECTS.OBJECT_NAME%TYPE;
V_CLOB CLOB;
CURSOR CUR_PROCEDURE IS
SELECT OBJECT_NAME
FROM DBA_OBJECTS
WHERE OWNER = Q'[SCOTT]'
AND OBJECT_TYPE = Q'[TABLE]';
BEGIN
DBMS_OUTPUT.ENABLE(BUFFER_SIZE => NULL);
SELECT DBMS_UTILITY.GET_TIME INTO V_BEGIN_TIME FROM DUAL;
OPEN CUR_PROCEDURE;
LOOP
FETCH CUR_PROCEDURE
INTO V_TABLE;
SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE => C_TABLE,
NAME => V_TABLE,
SCHEMA => C_USER)
INTO V_CLOB
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_CLOB||';');
EXIT WHEN CUR_PROCEDURE%NOTFOUND;
END LOOP;
SELECT DBMS_UTILITY.GET_TIME INTO V_END_TIME FROM DUAL;
SELECT TO_CHAR(V_END_TIME - V_BEGIN_TIME) 100
INTO V_RESULT
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
L_ERR_NUMS := SQLCODE;
DBMS_OUTPUT.PUT_LINE(L_ERR_NUMS || '-------' || SQLERRM);
END PROC_GET_TABLE_INFO;
END PKG_GET_INFO;
/
--------------------------------------------------------------------------------------------------------------
6.如何编译失效对象，这里为各位介绍几种重新编译失效对象的方法：
6.1利用DBMS_DDL.alter_compile方式重新编译失效对象
EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');
6.2自定义匿名块方式编译：
BEGIN
FOR CUR_REC IN (SELECT OWNER,
OBJECT_NAME,
OBJECT_TYPE,
DECODE(OBJECT_TYPE,
'PACKAGE',
1,
'PACKAGE BODY',
2,
2) AS RECOMPILE_ORDER
FROM DBA_OBJECTS
WHERE OBJECT_TYPE IN ('PACKAGE', 'PACKAGE BODY')
AND STATUS != 'VALID'
ORDER BY 4) LOOP
BEGIN
IF CUR_REC.OBJECT_TYPE = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || CUR_REC.OBJECT_TYPE || ' "' ||
CUR_REC.OWNER || '"."' || CUR_REC.OBJECT_NAME ||
'" COMPILE';
ELSE
EXECUTE IMMEDIATE 'ALTER PACKAGE "' || CUR_REC.OWNER || '"."' ||
CUR_REC.OBJECT_NAME || '" COMPILE BODY';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(CUR_REC.OBJECT_TYPE || ' : ' || CUR_REC.OWNER ||
' : ' || CUR_REC.OBJECT_NAME);
END;
END LOOP;
END;
6.3利用DBMS_UTILITY.compile_schema和UTL_RECOMP.recomp_parallel方式重新编译失效对象
BEGIN
DBMS_UTILITY.compile_schema('SCOTT'); --重新编译用户下所有的程序
END ;
/
BEGIN
SYS.UTL_RECOMP.recomp_parallel(threads =>8 ,schema =>'SCOTT' );--重新编译用户下所有的程序
END ;
6.4利用utlrp.sql重新编译失效对象
$ORACLE_HOME/rdbms/admin/utlrp.sql
注：utlrp。可以调用sql脚本重新编译数据库中的所有对象，就像使用utl_recomp包一样，除了utlrp.sql将查找并重新
编译数据库中的所有无效对象。utlrp的来源作为utl_recomp包的封装。
6.5重建数据库对象脚本：
------------------------------------------------------------------------------------------------
- --TroubleShooting :基于数据字典基表重建各类数据库对象脚本
------------------------------------------------------------------------------------------------
--重建dblink脚本
SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '
||L.PASSWORD||' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM link$ L, user$ U
WHERE L.OWNER# = U.USER#
--重建同义词
SELECT 'create or replace ' || DECODE(O.OWNER#, 1, ' public ') ||
' synonym ' || DECODE(O.OWNER#, 1, '', U.NAME || '.') || O.NAME ||
' for ' || S.OWNER || '.' || S.NAME || NVL2(S.NODE, '@', '') ||
S.NODE || ';'
FROM SYN$ S, OBJ$ O, USER$ U
WHERE S.OBJ# = O.OBJ#
AND O.DATAOBJ# IS NULL
AND S.OWNER = UPPER('&OWNER')
AND U.USER# = O.OWNER#
--重建视图
SELECT 'CREATE OR REPLACE VIEW ' || O.NAME || '(' ||
REPLACE(C.COLS, ', ', ', ' || CHR(10)) || ') ' || CHR(10) || 'AS' ||
CHR(10),
V.TEXT
FROM USER$ U,
OBJ$ O,
VIEW$ V,
(SELECT COL.OBJ#, COL.COLS
FROM (SELECT OBJ#,
COL#,
SUBSTR(SYS_CONNECT_BY_PATH(NAME, ', '), 2) COLS
FROM COL$
WHERE COL# > 0
START WITH COL# = 1
CONNECT BY PRIOR OBJ# = OBJ#
AND PRIOR COL# = COL# - 1) COL,
(SELECT OBJ#, COUNT(*) COLCNT
FROM COL$
WHERE COL# > 0
GROUP BY OBJ#) CN
WHERE COL.OBJ# = CN.OBJ#
AND COL.COL# = CN.COLCNT) C
WHERE U.USER# = O.OWNER#
AND O.OBJ# = C.OBJ#
AND V.OBJ# = O.OBJ#
AND U.NAME = UPPER('&NAME');
--重建job行为
SELECT JOB, LOWNER, INTERVAL#, NEXT_DATE, WHAT, SCHEDULER_FLAGS FROM JOB$
--重建索引
SELECT 'CREATE INDEX ' || I.NAME || ' ON ' || T.NAME || '(' || IDX.PATH || ');' INDEX_DDL
FROM USER$ U,
OBJ$ T,
OBJ$ I,
(SELECT I.PROPERTY,
I.BO#,
I.OBJ#,
C.POS#,
SUBSTR(SYS_CONNECT_BY_PATH(CN.NAME, ', '), 2) PATH
FROM IND$ I, ICOL$ C, COL$ CN
WHERE I.OBJ# = C.OBJ#
AND I.BO# = C.BO#
AND I.BO# = CN.OBJ#
AND C.COL# = CN.INTCOL#
START WITH C.POS# = 1
CONNECT BY NOCYCLE PRIOR I.OBJ# = I.OBJ#
AND PRIOR C.POS# = C.POS#) IDX,
(SELECT I.BO#, I.OBJ#, COUNT(*) COLCNT
FROM ICOL$ I
GROUP BY I.BO#, I.OBJ#) IDXC
WHERE U.USER# = T.OWNER#
AND IDX.BO# = T.OBJ#
AND IDX.OBJ# = I.OBJ#
AND IDX.BO# = IDXC.BO#
AND IDX.OBJ# = IDXC.OBJ#
AND IDX.POS# = IDXC.COLCNT
AND U.NAME = UPPER('&NAME')
ORDER BY T.NAME, I.NAME;
--重建触发器
SELECT 'CREATE OR REPLACE TRIGGER ' || TRIGGER_NAME || CHR(10) ||
DECODE(SUBSTR(TRIGGER_TYPE, 1, 1),
'A',
'AFTER ',
'B',
'BEFORE ',
'I',
'INSTEAD OF ') || TRIGGERING_EVENT || ' ON ' || TABLE_OWNER || '.' ||
TABLE_NAME || CHR(10) || REF_CLAUSE || CHR(10) ||
DECODE(INSTR(TRIGGER_TYPE, 'EACH ROW'), 0, NULL, 'FOR EACH ROW'),
TRIGGER_BODY
FROM (SELECT TRIGUSR.NAME OWNER,
TRIGOBJ.NAME TRIGGER_NAME,
DECODE(T.TYPE#,
0,
'BEFORE STATEMENT',
1,
'BEFORE EACH ROW',
2,
'AFTER STATEMENT',
3,
'AFTER EACH ROW',
4,
'INSTEAD OF',
'UNDEFINED') TRIGGER_TYPE,
DECODE(T.INSERT$ * 100 + T.UPDATE$ * 10 + T.DELETE$,
100,
'INSERT',
010,
'UPDATE',
001,
'DELETE',
110,
'INSERT OR UPDATE',
101,
'INSERT OR DELETE',
011,
'UPDATE OR DELETE',
111,
'INSERT OR UPDATE OR DELETE',
'ERROR') TRIGGERING_EVENT,
TABUSR.NAME TABLE_OWNER,
TABOBJ.NAME TABLE_NAME,
'REFERENCING NEW AS ' || T.REFNEWNAME || ' OLD AS ' ||
T.REFOLDNAME REF_CLAUSE,
T.WHENCLAUSE,
DECODE(T.ENABLED, 0, 'DISABLED', 1, 'ENABLED', 'ERROR') STATUS,
T.DEFINITION,
T.ACTION# TRIGGER_BODY
FROM OBJ$ TRIGOBJ,
OBJ$ TABOBJ,
TRIGGER$ T,
USER$ TABUSR,
USER$ TRIGUSR
WHERE (TRIGOBJ.OBJ# = T.OBJ# AND TABOBJ.OBJ# = T.BASEOBJECT AND
TABOBJ.OWNER# = TABUSR.USER# AND
TRIGOBJ.OWNER# = TRIGUSR.USER# AND
BITAND(T.PROPERTY, 63) < 8))
WHERE TABLE_OWNER = UPPER('&OWNER')
ORDER BY OWNER, TRIGGER_NAME
--重建序列
SELECT 'CREATE SEQUENCE ' || SEQ_NAME || ' MINVALUE ' || MINVAL ||
' MAXVALUE ' || MAXVAL || ' START WITH ' || LASTVAL || ' ' || CYC || ' ' || ORD ||
DECODE(SIGN(CACHE), 1, ' CACHE ' || CACHE, 'NOCACHE') || ';' SEQ_DDL
FROM (SELECT U.NAME OWNER,
O.NAME SEQ_NAME,
S.MINVALUE MINVAL,
S.MAXVALUE MAXVAL,
S.INCREMENT$ INC,
DECODE(S.CYCLE#, 0, 'NOCYCLE', 1, 'CYCLE ') CYC,
DECODE(S.ORDER$, 0, 'NOORDER', 1, 'ORDER ') ORD,
S.CACHE,
S.HIGHWATER LASTVAL
FROM SEQ$ S, OBJ$ O, USER$ U
WHERE U.USER# = O.OWNER#
AND O.OBJ# = S.OBJ#
AND U.NAME = UPPER('&NAME'))
--重建存储过程
SELECT DECODE(S.LINE, 1, 'CREATE OR REPLACE ') || SOURCE SOURCE
FROM USER$ U, OBJ$ O, SOURCE$ S
WHERE U.USER# = O.OWNER#
AND O.OBJ# = S.OBJ#
AND U.NAME = '&NAME'
ORDER BY S.OBJ#, S.LINE;
--------------------------------------------------------------------------------------------------------------
补充说明，例如如下报错信息发生在datapump期间：
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_METADATA_UTIL
ORA-01775: looping chain of synonyms
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_METADATA_UTIL"
处理思路：
所需的DBMS_METADATA_INT和DBMS_METADATA_INT包处于无效状态，那么首先需要验证无效对象信息：
select owner, object_name, status
from sys.dba_objects where
status = 'INVALID'
and owner in ('SYS','SYSTEM')
order by 1;
OWNER OBJECT_NAME STATUS
------ ------------------- -------
SYS DBMS_METADATA_INT INVALID
SYS DBMS_METADATA_UTIL INVALID
以SYS用户的身份运行以下脚本以验证包并重新运行DataPump导入作业
SQL> $ORACLE_HOME/rdbms/admin/prvtmeti.plb
SQL> $ORACLE_HOME/rdbms/admin/prvtmetu.plb
SQL> $ORACLE_HOME/rdbms/admin/utlrp.sql
--------------------------------------------------------------------------------------------------------------
<点亮梦想.拒绝平庸>
