ORA-03001: unimplemented feature

Posted: March 12, 2013 in Database General
Tags: ,

Recently, on 10.2.0.4 database, i noticed “ORA-03001: unimplemented feature” error for one of our schema stats gather job. Job is simply collecting the stats for a schema and i got the error as below:

SQL> BEGIN DBMS_STATS.GATHER_SCHEMA_STATS (‘ORASTEPS’, CASCADE => TRUE, METHOD_OPT => ‘FOR ALL COLUMNS SIZE REPEAT’); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at “SYS.DBMS_STATS”, line 13591
ORA-06512: at “SYS.DBMS_STATS”, line 13937
ORA-06512: at “SYS.DBMS_STATS”, line 14015
ORA-06512: at “SYS.DBMS_STATS”, line 13974
ORA-06512: at line 1

Little time on google and then oracle supports confirms that it’s a bug 5767661 which is fixed in 10.2.0.5 and 11g. Patch is also available for my current version i.e. 10.2.0.4. Please contact oracle support for details related to patch.

As per oracle support document 559389.1, problem is with functional index having non literal values. Options available is to apply patch or change the non literal value to literal value. Below is one index which is causing problem in my case. For details of finding the exact index causing problem, is included in oracle support document 559389.1.

CREATE INDEX ORASTEPS.DOC_EXT_STA_IDX ON ORASTEPS.OPEN_DOCUMENTS (DOC_EXT_STATUS, 1);

I need to change the numeric 1 to literal ‘1’ for workaround. Drop the old index and create new index as below:

CREATE INDEX ORASTEPS.DOC_EXT_STA_IDX ON ORASTEPS.OPEN_DOCUMENTS (DOC_EXT_STATUS, ‘1’);

Again i ran the gather schema stats job and this time it got completed successfuly.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s