共计 3517 个字符，预计需要花费 9 分钟才能阅读完成。
本篇内容介绍了“数据库中失效对象编译的方法教程”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
--------------------------------------------------------
-- DDL for Table RECOMPILE_LOG
--------------------------------------------------------
CREATE TABLE RECOMPILE_LOG
( RDATE DATE,
ERRMSG VARCHAR2(200 BYTE)
) ;
--------------------------------------------------------
-- DDL for Table RECOMPILE_TYPE_INFO
--------------------------------------------------------
CREATE TABLE RECOMPILE_TYPE_INFO
( TYPE VARCHAR2(30 BYTE),
PROCESS_MODE VARCHAR2(30 BYTE),
BASE_TABLE VARCHAR2(30 BYTE)
) ;
Insert into EODA.RECOMPILE_TYPE_INFO (TYPE,PROCESS_MODE,BASE_TABLE) values ( TRIGGER , COMPILE , DBA_DEPENDENCIES
Insert into EODA.RECOMPILE_TYPE_INFO (TYPE,PROCESS_MODE,BASE_TABLE) values ( PROCEDURE , COMPILE , DBA_DEPENDENCIES
Insert into EODA.RECOMPILE_TYPE_INFO (TYPE,PROCESS_MODE,BASE_TABLE) values ( FUNCTION , COMPILE , DBA_DEPENDENCIES
Insert into EODA.RECOMPILE_TYPE_INFO (TYPE,PROCESS_MODE,BASE_TABLE) values ( PACKAGE , COMPILE , DBA_DEPENDENCIES
Insert into EODA.RECOMPILE_TYPE_INFO (TYPE,PROCESS_MODE,BASE_TABLE) values ( TYPE , COMPILE , DBA_DEPENDENCIES
Insert into EODA.RECOMPILE_TYPE_INFO (TYPE,PROCESS_MODE,BASE_TABLE) values ( INDEX , REBUILD ONLINE , DBA_INDEXES
--------------------------------------------------------
-- DDL for Procedure RECOMPILE_OBJECTS
--------------------------------------------------------
set define off;
CREATE OR REPLACE EDITIONABLE PROCEDURE RECOMPILE_OBJECTS (RIO_OWNER VARCHAR2,
RIO_OBJECT_NAME VARCHAR2) AS
COMPILE_SQL VARCHAR2(500);
P_OWNER VARCHAR2(30) := UPPER(RIO_OWNER);
P_OBJECT_NAME VARCHAR2(30) := UPPER(RIO_OBJECT_NAME);
BEGIN
FOR RIO_TYPE IN (SELECT TYPE, PROCESS_MODE, BASE_TABLE
FROM RECOMPILE_TYPE_INFO) LOOP
IF RIO_TYPE.BASE_TABLE = DBA_DEPENDENCIES THEN
FOR DBA_DEP IN (SELECT OWNER, NAME
FROM DBA_DEPENDENCIES
WHERE TYPE = RIO_TYPE.TYPE
AND REFERENCED_OWNER = P_OWNER
AND REFERENCED_NAME = P_OBJECT_NAME) LOOP
FOR DBA_OBJ IN (SELECT STATUS
FROM DBA_OBJECTS
WHERE OWNER = DBA_DEP.OWNER
AND OBJECT_NAME = DBA_DEP.NAME) LOOP
IF DBA_OBJ.STATUS = INVALID THEN
BEGIN
COMPILE_SQL := ALTER || RIO_TYPE.TYPE || ||
DBA_DEP.OWNER || . || DBA_DEP.NAME || ||
RIO_TYPE.PROCESS_MODE;
EXECUTE immediate COMPILE_SQL;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO recompile_log
(rdate, errmsg)
VALUES
(sysdate, COMPILE_SQL);
END;
END IF;
END LOOP;
END LOOP;
END IF;
IF RIO_TYPE.BASE_TABLE = DBA_INDEXES THEN
FOR DBA_IND IN (SELECT OWNER, INDEX_NAME, PARTITIONED, STATUS
FROM DBA_INDEXES
WHERE TABLE_OWNER = P_OWNER
AND TABLE_NAME = P_OBJECT_NAME) LOOP
IF DBA_IND.PARTITIONED = NO AND DBA_IND.STATUS = INVALID THEN
BEGIN
COMPILE_SQL := ALTER || RIO_TYPE.TYPE || ||
DBA_IND.OWNER || . || DBA_IND.INDEX_NAME || ||
RIO_TYPE.PROCESS_MODE;
EXECUTE immediate COMPILE_SQL;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO recompile_log
(rdate, errmsg)
VALUES
(sysdate, COMPILE_SQL);
END;
END IF;
IF DBA_IND.PARTITIONED = YES THEN
FOR IND_PAR IN (SELECT PARTITION_NAME, STATUS
FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER = DBA_IND.OWNER
AND INDEX_NAME = DBA_IND.INDEX_NAME) LOOP
IF IND_PAR.STATUS = UNUSABLE THEN
BEGIN
COMPILE_SQL := ALTER || RIO_TYPE.TYPE || ||
DBA_IND.OWNER || . || DBA_IND.INDEX_NAME ||
PARTITION || IND_PAR.PARTITION_NAME ||
ONLINE
EXECUTE immediate COMPILE_SQL;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO recompile_log
(rdate, errmsg)
VALUES
(sysdate, COMPILE_SQL);
END;
END IF;
END LOOP;
END IF;
END LOOP;
END IF;
END LOOP;
/“数据库中失效对象编译的方法教程”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
正文完
