Oracle Database Error ORA-02327 When Rebuilding P6 Indexes (Doc ID 2671898.1)

Last updated on AUGUST 02, 2023

Applies to:

Symptoms

When attempting to rebuild all indexes in the P6 schema using the resulting scripts from the following,
select 'alter index '||owner||'.'||index_name ||' rebuild online nologging;' from dba_indexes where owner='ADMUSER';

The following error is thrown on all ADMUSER.SYS_%%%$$ indexes.

NOTE: ADMUSER is the P6 schema owner.

ERROR
-----------------------
alter index ADMUSER.SYS_%%%$$ rebuild online nologging
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Connect to the database as SYSTEM or SYS.
2. Execute the following query.
    select 'alter index '||owner||'.'||index_name ||' rebuild online nologging;' from dba_indexes where owner='ADMUSER';
3. Execute the results of the query from step 2.


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, errors are received.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.