PL/SQL - Stored Procedure that turn indexes status from UNUSABLE to VALID

Question

I've got a cursor that returns indexes from user_indexes with status = 'UNUSABLE' and print them in the output tab:

DECLARE
    cur sys_refcursor;
BEGIN
    FOR iStat IN (SELECT INDEX_NAME FROM USER_INDEXES WHERE STATUS = 'UNUSABLE' ORDER BY INDEX_NAME) LOOP
            DBMS_OUTPUT.PUT_LINE('ALTER INDEX '||iStat.INDEX_NAME||' REBUILD');
    END LOOP;
END;

These are some of the list of indexes that return:

ALTER INDEX II_LKP_DRV_POLRIECUB_REA_MISC REBUILD;
ALTER INDEX IND1_CM_FACT_RVA_COMISION REBUILD;
ALTER INDEX IND2_CM_FACT_RVA_COMISION REBUILD;
ALTER INDEX INDX_SN_FACT_RECLA_ADJ_SINI REBUILD;

I did a stored procedure to do an alter table on those indexes returned at the cursor (I only used 2 indexes for testing purposes):

CREATE OR REPLACE PROCEDURE status_index
AS
cur sys_refcursor;

BEGIN
    FOR iStat IN (SELECT INDEX_NAME FROM USER_INDEXES WHERE INDEX_NAME IN ('II_LKP_DRV_POLRIECUB_REA_MISC', 'INDX_SN_FACT_RECLA_ADJ_SINI') AND STATUS = 'UNUSABLE' ORDER BY INDEX_NAME) LOOP
            'ALTER INDEX ' || iStat.INDEX_NAME || ' REBUILD';
    END LOOP;  
END;

It works if I use the DBMS_OUTPUT.PUT_LINE, but if I use theALTER INDEX at the time I run the procedure, it throws the error below:

ORA-06550: line 2, column 5
PLS-00905: object ECCO_A.STATUS_INDEX is invalid
ORA-06550: line 2, column 5
PL/SQL: Statement ignored

I would appreciate if someone could help me fix this error.

As an aside, what is the purpose of the sys_refcursor that you declare at the beginning of the block? You never reference it. Also, your code would be much more readable if you were to adopt some good formatting rules instead of writing your SELECT as one long line. — EdStevens, Mar 17 '20 at 17:28

kevinskio · Accepted Answer · 2020-03-17T18:28:25.683

3

The alter index statement is a Data Definition Language statement. It can only run inside a DML (Data manipulation language) block/procedure/function if you use Dynamic SQL. Your DML also acts as an implict commit which can surprise you if this is one of a chain of code blocks being executed.

BEGIN
    FOR iStat IN (SELECT INDEX_NAME FROM USER_INDEXES 
 WHERE INDEX_NAME IN ('II_LKP_DRV_POLRIECUB_REA_MISC', 
 'INDX_SN_FACT_RECLA_ADJ_SINI') 
 AND STATUS = 'UNUSABLE' 
 ORDER BY INDEX_NAME) 
LOOP
               EXECUTE IMMEDIATE 'ALTER INDEX ' || iStat.INDEX_NAME || ' REBUILD';
        END LOOP;  
    END;

See here and here. You might also consider dealing with whatever is making your indexes unusable in the first place as that might allow you not to require this maintenance.

edited Mar 17 '20 at 18:28

answered Mar 17 '20 at 16:49

kevinskio

4,431
1
22
36

1

Agree that you should investigate the underlying cause. It's not normal for indexes to become invalid. – APC Mar 17 '20 at 18:21
Note, an index can be `UNUSABLE` on purpose. One example are [Partial Indexes](https://docs.oracle.com/database/121/VLDBG/GUID-256BA7EE-BF49-42DE-9B38-CD2480A73129.htm#VLDBG14102) – Wernfried Domscheit Mar 17 '20 at 18:47

PL/SQL - Stored Procedure that turn indexes status from UNUSABLE to VALID

1 Answers1