27.7 Verifying Invalid Objects and Indexes in the Oracle Database

To verify if there are any Invalid objects and Indexes in ZENworks Control Center that uses the Oracle database for ZENworks or Audit schema, 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';