189 8069 5689
本篇内容介绍了“数据库中失效对象编译的方法教程”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
--------------------------------------------------------
-- 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;
END;
/“数据库中失效对象编译的方法教程”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联-成都网站建设公司网站，小编将为大家输出更多高质量的实用文章！