SQL & PL/SQL

Hi All,

I am using ORACLE 11g R2.

While running gather stats on partitioned tables, I am getting the below error.
ORA-08104: this index object 264974 is being online built or rebuilt

Below is the code that I am using:

begin

FOR P IN (SELECT TABLE_NAME, INDEX_NAME
FROM USER_INDEXES
WHERE TABLE_OWNER = V_OWNNAME
AND STATUS = 'UNUSABLE'
)
LOOP



EXECUTE IMMEDIATE 'ALTER INDEX '||P.INDEX_NAME||' REBUILD PARALLEL 4 ' ;

END LOOP ;

FOR Q IN (SELECT INDEX_NAME,PARTITION_NAME
FROM USER_IND_PARTITIONS
WHERE STATUS = 'UNUSABLE'
)

LOOP


EXECUTE IMMEDIATE 'ALTER INDEX '||Q.INDEX_NAME||' REBUILD PARTITION '|| Q.PARTITION_NAME || ' PARALLEL 4' ;

END LOOP ;

FOR R IN (SELECT INDEX_NAME,SUBPARTITION_NAME
FROM USER_IND_SUBPARTITIONS
WHERE STATUS = 'UNUSABLE'
)

LOOP


EXECUTE IMMEDIATE 'ALTER INDEX '||R.INDEX_NAME||' REBUILD SUBPARTITION '|| R.SUBPARTITION_NAME || ' PARALLEL 4' ;

DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'ABC'
,TABNAME => 'XYZ'
,CASCADE => DBMS_STATS.AUTO_CASCADE
,DEGREE => DBMS_STATS.AUTO_DEGREE
,ESTIMATE_PERCENT => DBMS_STATS.auto_sample_size
,GRANULARITY => 'AUTO'
);

end;

Apart from this code, there is no other session that uses the indexes. No session is being killed while using this code. All the basic issues that results in ORA-08104 are being taken care off, still this error is coming. Also the INCREMENTAL option is set to TRUE for all the partitioned tables.

Please help.