手工运行oracle job时报ora-03001,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/，如需转载，请注明出处，否则将追究法律责任。