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