本篇內(nèi)容介紹了“數(shù)據(jù)庫(kù)中失效對(duì)象編譯的方法教程”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
重慶分公司
重慶分公司
--------------------------------------------------------
-- 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;
/“數(shù)據(jù)庫(kù)中失效對(duì)象編譯的方法教程”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)-成都網(wǎng)站建設(shè)公司網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！
