ORA-03001: unimplemented feature 報錯處理

ORA-03001: unimplemented feature 報錯處理


客戶資料庫出現 ORA-03001錯誤。
問題重現如下：


SQL> create table drive(id int);
Table created.
SQL> insert into drive select  rownum from all_objects where rownum<=10;
10 rows created.
SQL> commit;
Commit complete.
SQL> create table inner_table as select * from all_objects where rownum<=100000;
Table created.
SQL> exec dbms_stats.gather_schema_stats(user,degree=>2);
BEGIN dbms_stats.gather_schema_stats(user,degree=>2); 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
SQL> ! oerr ora 3001
03001, 00000, "unimplemented feature"
// *Cause:  This feature is not implemented.
// *Action:  None.




考慮啟用errorstack的跟蹤來找到出現問題的SQL語句。
SQL> alter session set events '3001 trace name errorstack level 3';
Session altered.
SQL> exec dbms_stats.gather_schema_stats(user);
BEGIN dbms_stats.gather_schema_stats(user); 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


在跟蹤檔案裡可以看到如下資訊：
Wed Aug 18 15:27:48 2010
Errors in file /u01/oracle/admin/uatdb2/udump/uatdb2_ora_18481228.trc:
ORA-03001: unimplemented feature


開啟跟蹤檔案：
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
ORACLE_HOME = /u01/oracle/product/db10gr2
System name:    AIX
Node name:      SXZYXDB002
Release:        3
Version:        5
Machine:        00C79F504C00
Instance name: uatdb2
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 18481228, image: oracle@SXZYXDB002 (TNS V1-V3)
*** 2010-08-18 15:25:20.399
*** ACTION NAME:() 2010-08-18 15:25:20.394
*** MODULE NAME:(SQL*Plus) 2010-08-18 15:25:20.394
*** SERVICE NAME:(SYS$USERS) 2010-08-18 15:25:20.394
*** SESSION ID:(1066.21407) 2010-08-18 15:25:20.394
KGX cleanup...
KGX Atomic Operation Log 7000004ed16f518
 Mutex 7000004ed3e5ee8(1066, 0) idn 0 oper EXAM
 Cursor Parent uid 1066 efd 5 whr 26 slp 0
 oper=DEFAULT pt1=1105a58f0 pt2=1105a54e8 pt3=1104af6a8
 pt4=0 u41=0 stt=0
*** 2010-08-18 15:27:48.552
ksedmp: internal or fatal error
ORA-03001: unimplemented feature
Current SQL statement for this session:
select /*+ parallel_index(t,"ABC_IDX",2) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index_ffs(t,"ABC_IDX") */ 
count(*) as nrw,count(distinct sys_op_lbid(60478,'L',t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend(1)||sys_op_descend("OBJECT_NAME"))) as ndk,
sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "TEST"."ABC" t where 1 is not null or "OBJECT_NAME" is not null
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
7000004f38a8a20      9598  package body SYS.DBMS_STATS
7000004f38a8a20     10157  package body SYS.DBMS_STATS
7000004f38a8a20     10792  package body SYS.DBMS_STATS
7000004f38a8a20     13408  package body SYS.DBMS_STATS
7000004f38a8a20     13546  package body SYS.DBMS_STATS
。。。。。。。。。。。。。。
。。。。。。。。。。。。。。。。




可以看到ORACLE在執行到下面這條SQL語句報錯了
select /*+ parallel_index(t,"ABC_IDX",2) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index_ffs(t,"ABC_IDX") */ 
count(*) as nrw,count(distinct sys_op_lbid(60478,'L',t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend(1)||sys_op_descend("OBJECT_NAME"))) as ndk,
sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "TEST"."ABC" t where 1 is not null or "OBJECT_NAME" is not null
這條SQL語句是收集索引ABC_IDX的統計資訊。nrw 相當於user_indexes.num_rows,nlb相當於user_indexes.leaf_blocks,ndk=user_indexes.num_distinct,clf=clustering_factor


單獨拿出來在SQLPLUS下執行了一下 ，也報錯。
SQL> select /*+ parallel_index(t,"ABC_IDX",2) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index_ffs(t,"ABC_IDX") */ 
  2  count(*) as nrw,count(distinct sys_op_lbid(60478,'L',t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend(1)||sys_op_descend("OBJECT_NAME"))) as ndk,
  3  sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "TEST"."ABC" t where 1 is not null or "OBJECT_NAME" is not null;
select /*+ parallel_index(t,"ABC_IDX",2) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index_ffs(t,"ABC_IDX") */
*
ERROR at line 1:
ORA-03001: unimplemented feature




檢視這一下這個索引的資訊：
SQL> SELECT INDEX_NAME,INDEX_TYPE FROM USER_INDEXES WHERE INDEX_NAME='ABC_IDX';
INDEX_NAME           INDEX_TYPE
-------------------- --------------------
ABC_IDX              FUNCTION-BASED NORMAL
SQL> COL COLUMN_EXPRESSION FORMAT A20
SQL> SELECT INDEX_NAME,COLUMN_EXPRESSION,COLUMN_POSITION FROM USER_IND_EXPRESSIONS WHERE INDEX_NAME='ABC_IDX';
INDEX_NAME           COLUMN_EXPRESSION    COLUMN_POSITION
-------------------- -------------------- ---------------
ABC_IDX              1                                  1
SQL> SELECT COLUMN_NAME,COLUMN_POSITION FROM USER_IND_COLUMNS WHERE INDEX_NAME='ABC_IDX';
COLUMN_NAME          COLUMN_POSITION
-------------------- ---------------
SYS_NC00014$                       1
OBJECT_NAME                        2


這是我當時建立的一個含有常數的索引，第一個列是1，第二個列是OBJECT_NAME。ORACLE給列1起來一個SYS_NC00014$ 的列名。而且在USER_IND_EXPRESSIONS 的COLUMN_EXPRESSION 只記錄了一個1。
懷疑ORACLE在處理這樣的索引的時候出現問題了。


刪掉這個索引，在收集一下看看：
SQL> DROP INDEX ABC_IDX;
Index dropped.
SQL>  exec dbms_stats.gather_schema_stats(user);
PL/SQL procedure successfully completed.
可以看到此成功執行。
在此嘗試建立常數索引看看：
SQL> create index abc_idx on abc(1,object_name);
Index created.
SQL> SELECT INDEX_NAME,INDEX_TYPE FROM USER_INDEXES WHERE INDEX_NAME='ABC_IDX';
INDEX_NAME           INDEX_TYPE
-------------------- ---------------------
ABC_IDX              FUNCTION-BASED NORMAL
SQL> SELECT INDEX_NAME,COLUMN_EXPRESSION,COLUMN_POSITION FROM USER_IND_EXPRESSIONS WHERE INDEX_NAME='ABC_IDX';
INDEX_NAME           COLUMN_EXPRESSION    COLUMN_POSITION
-------------------- -------------------- ---------------
ABC_IDX              1                                  1
SQL> SELECT COLUMN_NAME,COLUMN_POSITION FROM USER_IND_COLUMNS WHERE INDEX_NAME='ABC_IDX';
COLUMN_NAME          COLUMN_POSITION
-------------------- ---------------
SYS_NC00014$                       1
OBJECT_NAME                        2
SQL> exec dbms_stats.gather_schema_stats(user);
BEGIN dbms_stats.gather_schema_stats(user); 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


看了就是這個索引的問題了。單獨收集這個索引也是有問題的：
SQL> exec dbms_stats.gather_index_stats(user,'ABC_IDX');
BEGIN dbms_stats.gather_index_stats(user,'ABC_IDX'); END;
*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 10872
ORA-06512: at "SYS.DBMS_STATS", line 10896
ORA-06512: at line 1
確實ORACLE在收集常數索引會有問題，不知道是不是個BUG。
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production


檢視metalink
ORA-03001報錯一般為自動蒐集統計資訊時促發BUG 6011068 造成，可打 Patch 5767661修復或升級到10205，或參考(文件 ID 559389.1)解決。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21752515/viewspace-1065766/，如需轉載，請註明出處，否則將追究法律責任。