To verify if there are any Invalid objects and Indexes in ZENworks or Audit schema, you need to run the following queries:
NOTE:Do NOT use SQuirreL
SELECT * FROM USER_OBJECTS WHERE STATUS='INVALID';SELECT * FROM USER_INDEXES WHERE FUNCIDX_STATUS='DISABLED';SELECT * FROM USER_INDEXES WHERE STATUS='INVALID';SELECT * FROM USER_IND_PARTITIONS WHERE STATUS NOT IN ('N/A','USABLE');SELECT * FROM USER_IND_SUBPARTITIONS WHERE STATUS NOT IN ('USABLE');SELECT * FROM USER_CONSTRAINTS WHERE STATUS!='ENABLED';
The above queries should NOT return any records.If these queries return any records, you need to run the below scripts in the respective schema:
DECLARE V_CNT NUMBER;BEGIN SELECT COUNT(1) INTO V_CNT FROM USER_OBJECTS WHERE STATUS ='INVALID' AND OBJECT_TYPE IN ('PROCEDURE','TRIGGER','FUNCTION','VIEW'); WHILE V_CNT > 0 LOOP FOR REC IN (SELECT * FROM USER_OBJECTS WHERE STATUS ='INVALID' AND OBJECT_TYPE IN ('PROCEDURE','TRIGGER','FUNCTION','VIEW') ) LOOP BEGIN EXECUTE IMMEDIATE 'ALTER '||REC.OBJECT_TYPE||' "'||REC.OBJECT_NAME||'" COMPILE'; EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; SELECT COUNT(1) INTO V_CNT FROM USER_OBJECTS WHERE STATUS ='INVALID' AND OBJECT_TYPE IN ('PROCEDURE','TRIGGER','FUNCTION','VIEW'); END LOOP;END;/DECLARE V_CNT NUMBER;BEGIN SELECT COUNT(1) INTO V_CNT FROM USER_INDEXES WHERE FUNCIDX_STATUS='DISABLED' AND TEMPORARY ='N' AND INDEX_TYPE! ='LOB' AND PARTITIONED ='NO' AND TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TEMPORARY='N' ) AND TABLE_NAME NOT IN ('PLAN_TABLE','ZENUPGRADELOG','ZENUPG_INDEX'); WHILE V_CNT > 0 LOOP FOR REC IN (SELECT * FROM USER_INDEXES WHERE FUNCIDX_STATUS='DISABLED' AND TEMPORARY ='N' AND INDEX_TYPE! ='LOB' AND PARTITIONED ='NO' AND TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TEMPORARY='N' ) AND TABLE_NAME NOT IN ('PLAN_TABLE','ZENUPGRADELOG','ZENUPG_INDEX') ) LOOP EXECUTE IMMEDIATE 'ALTER INDEX '||REC.INDEX_NAME||' ENABLE'; END LOOP; SELECT COUNT(1) INTO V_CNT FROM USER_INDEXES WHERE FUNCIDX_STATUS='DISABLED' AND TEMPORARY ='N' AND INDEX_TYPE! ='LOB' AND PARTITIONED ='NO' AND TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TEMPORARY='N' ) AND TABLE_NAME NOT IN ('PLAN_TABLE','ZENUPGRADELOG','ZENUPG_INDEX'); END LOOP;END;/BEGIN FOR REC1 IN (SELECT * FROM USER_INDEXES WHERE PARTITIONED='YES' AND TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TEMPORARY='N' ) AND TABLE_NAME NOT IN ('PLAN_TABLE','ZENUPGRADELOG','ZENUPG_INDEX') ) LOOP IF REC1.STATUS='N/A' THEN FOR REC2 IN (SELECT * FROM USER_IND_PARTITIONS WHERE INDEX_NAME=REC1.INDEX_NAME AND STATUS! ='USABLE' ) LOOP IF REC1.STATUS='N/A' THEN FOR REC3 IN (SELECT * FROM USER_IND_SUBPARTITIONS WHERE INDEX_NAME =REC2.INDEX_NAME AND PARTITION_NAME=REC2.PARTITION_NAME AND STATUS! ='USABLE' ) LOOP EXECUTE IMMEDIATE 'ALTER INDEX '||REC3.INDEX_NAME||' REBUILD SUBPARTITION '||REC3.SUBPARTITION_NAME; END LOOP; ELSE EXECUTE IMMEDIATE 'ALTER INDEX '||REC2.INDEX_NAME||' REBUILD PARTITION '||REC2.PARTITION_NAME; END IF; END LOOP; END IF; END LOOP; FOR REC IN (SELECT * FROM USER_INDEXES WHERE FUNCIDX_STATUS!='ENABLED' ) LOOP EXECUTE IMMEDIATE 'ALTER INDEX '||REC.INDEX_NAME||' ENABLE'; END LOOP;END;/
Verify the invalid objects after executing above scripts. The following queries should NOT return any records:
SELECT * FROM USER_OBJECTS WHERE STATUS='INVALID';SELECT * FROM USER_INDEXES WHERE FUNCIDX_STATUS='DISABLED';SELECT * FROM USER_INDEXES WHERE STATUS='INVALID';SELECT * FROM USER_IND_PARTITIONS WHERE STATUS NOT IN ('N/A','USABLE');SELECT * FROM USER_IND_SUBPARTITIONS WHERE STATUS NOT IN ('USABLE');SELECT * FROM USER_CONSTRAINTS WHERE STATUS!='ENABLED';