数据库中失效对象编译的方法教程

本篇内容介绍了“数据库中失效对象编译的方法教程”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

-------------------------------------------------------- --  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_NAMEVARCHAR2) 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     IFRIO_TYPE.BASE_TABLE =DBA_DEPENDENCIES THEN       FOR DBA_DEP IN (SELECT OWNER, NAME                         FROM DBA_DEPENDENCIES                        WHERE TYPE= RIO_TYPE.TYPEAND 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                          ANDTABLE_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 immediateCOMPILE_SQL;           EXCEPTION             WHEN OTHERS THENINSERT INTOrecompile_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                            WHEREINDEX_OWNER = DBA_IND.OWNERAND INDEX_NAME = DBA_IND.INDEX_NAME) LOOP             IF IND_PAR.STATUS = UNUSABLE THEN               BEGINCOMPILE_SQL :=ALTER  || RIO_TYPE.TYPE ||  ||                                DBA_IND.OWNER || .|| DBA_IND.INDEX_NAME || PARTITION  || IND_PAR.PARTITION_NAME ||                                 ONLINE;                 EXECUTE immediateCOMPILE_SQL;               EXCEPTION                 WHEN OTHERS THENINSERT INTOrecompile_log                     (rdate, errmsg)VALUES                     (sysdate, COMPILE_SQL);               END;             END IF;           END LOOP;         END IF;       END LOOP;     END IF;   END LOOP; END; /

“数据库中失效对象编译的方法教程”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！