20.3 Verifying Invalid Objects and Indexes in the Oracle Database

If you encounter Invalid Objects or Invalid Index in ZENworks Control Center or ZENworks logs, you can use the following queries to verify if there are any Invalid objects and Indexes in the Oracle database

NOTE:

  • Do NOT use SQuirreL

SELECT * FROM USER_OBJECTS WHERE STATUS!='VALID';
SELECT * FROM USER_INDEXES WHERE FUNCIDX_STATUS='DISABLED';
SELECT * FROM USER_INDEXES WHERE STATUS NOT IN ('VALID','USABLE', 'N/A');
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, then 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';