Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the method course of compiling failed objects in database". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

-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; 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 This is the end of END LOOP;END;/ 's "method tutorial for compiling failed objects in Database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.