ORA-03001: Unimplemented Feature when Running DBMS_STATS.GATHER_INDEX_STATS [ID 559389.1]

ORA-03001: Unimplemented Feature when Running DBMS_STATS.GATHER_INDEX_STATS [ID 559389.1]  

In this Document
  Symptoms
  Cause
  Solution
  References

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 24-Jan-2012***
Symptoms
When running dbms_stats.gather_schema_stats or dbms_stats.gather_index_stats to collect the statistics the following error occurs:

ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 10640
ORA-06512: at "SYS.DBMS_STATS", line 10664
ORA-06512: at line 1

Cause
This issue is caused by unpublished bug
Bug 6011068 - ORA-3001 DBMS_STATS.GATHER_TABLE_STATS
which points to base bug
Bug 5767661 - Wrong results with function based index.

Solution
Please follow up the below steps to troubleshoot this issue:

1.
SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;
SQL> alter session set events '3001 trace name ERRORSTACK level 3';

Then reproduce this issue in this sqlplus session. The ORA-3001 errorstack trace file will be generated under the user_dump_dest folder.


2. The ORA-3001 errorstack trace file will display the following informations:

*** ACTION NAME:() 2008-03-24 09:27:11.573
*** MODULE NAME:(SQL*Plus) 2008-03-24 09:27:11.573
*** SERVICE NAME:(SYS$USERS) 2008-03-24 09:27:11.573
*** SESSION ID:(61.305) 2008-03-24 09:27:11.573
*** 2008-03-24 09:27:11.573
ksedmp: internal or fatal error
ORA-03001: unimplemented feature

Current SQL statement for this session:

select /*+ parallel_index(t,"",16) dbms_stats cursor_sharing_exact
use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand................

----- Call Stack Trace -----
ksedmp ksddoa ksdpcg ksdpec PGOSF152_ksfpec kgesev kgesec0 qcuErroer qcuErroep erroep qeaeCcg qesaInitAggs qergsStart selexe opiexe opipls opiodr rpidrus
rpidru rpiswu2 rpidrv psddr0 psdnal pevm_EXIM pfrinstr_EXIM pfrrun_no_tool pfrrun plsql_run peicnt kkxexe opiexe kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o opimai_real opimai OracleThreadStart


3. Issue the following sql statement to get the index DDL:

SQL>select dbms_metadata.get_ddl('INDEX', '', '') from dual;


4. If the index DDL is using the numeric literal but not the character literal, then you are hitting Bug 5767661.
For example

CREATE INDEX ""."" ON ""."" ("", 1)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "INDX"


Please follow up the below steps to solve this issue:

1. The base Bug 5767661 is fixed in 10.2.0.5, 11.1.0.7 and 11.2 patch sets. Upgrading to any of those versions / patch sets will resolve this problem.

OR

2. Apply one-off Patch 5767661, available for 9.2.0.8 and 10.2.0.4.

OR

3. If the one-off patch is not available for your platform, then you can use the below workaround to solve this issue:

Workaround:

3.1. Drop the index.

3.2. Replace the numeric literal with character literal to recreate the index. For example,

CREATE INDEX ""."" ON ""."" ("", '1')
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "INDX"

3.3. Run the dbms_stats package again to check whether this issue is solved.

References
BUG:5767661 - WRONG RESULTS WITH FUNCTION BASED INDEX