Partition corruption: ORA-8103 Object no longer exists

Recently we observed intermittent issues on on of the table that is hash partitioned with 64 partitions on item column.

The same table has  composite local index on 4 columns out of that one is part of partition.  This table is part of daily batch and getting truncated during start of batch and consists of output data of batch.

We identified and able to replicate the below query is failing with given exception.

 select distinct optionset from udt_skups;

These are the four columns those are part of local index.
OPTIONSET,STARTDATE ,ITEM, LOC

below queries are going fine
select  distinct OPTIONSET,STARTDATE ,ITEM, LOC from udt_skups;

select *  from udt_skups where rownum<10


select distinct userid from udt_skups;

select  optionset from udt_skups;

below queries are failing;

 select distinct optionset from udt_skups;

 select distinct OPTIONSET,STARTDATE ,ITEM from udt_skups;
--or rest other combinations on local index.

latter to identify corrupted partitions , executed below query on each partition.

This query went fine on partition 5
SELECT  count(OPTIONSET) FROM udt_skups PARTITION (P5) where rownum<5
but
SELECT  count(OPTIONSET) FROM udt_skups PARTITION (P5)
failed with same error.

So this is pretty clear that some of the partitions are corrupted in table and if those partitions hold data retrieved by query, it will fail.

To automate partition corruption, following code is written.

CREATE OR REPLACE PROCEDURE proc_check_partition_health (
   tabname   IN VARCHAR,
   colname   IN VARCHAR)
IS
   i_count   NUMBER;
BEGIN
   FOR i IN (SELECT partition_name
               FROM user_tab_partitions
              WHERE table_name = tabname)
   LOOP
      BEGIN
         EXECUTE IMMEDIATE
               'SELECT  count('
            || colname
            || ') FROM '
            || tabname
            || ' PARTITION ('
            || i.partition_name
            || ')'
            INTO i_count;

         DBMS_OUTPUT.put_line (
            'The count in partition' || i.partition_name || ' is ' || i_count);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
               'The  partition ' || i.partition_name || ' is corrupted');
      END;
   END LOOP;
END;
/
show errors

The proc takes input as partitioned table name and the column on which table is partitioned or any column that is part of local index.