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

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

----------------------------------------------------------  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;/

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