NVARCHAR2索引造成的ORA-964错误（二）

研究NVARCHAR2类型的时候，发现一个有趣的bug。

通过TRACE跟踪导致错误的原因。

输入NVARCHRA2类型字符串：http://yangtingkun.itpub.net/post/468/476812

NVARCHAR2索引造成的ORA-964错误（一）：http://yangtingkun.itpub.net/post/468/476871

 

 

本来写完上一篇文章，就打算继续通过TRACE根据的方式来查看导致问题的真正原因，不过恰好第二天是ITPUB的年会，就将这个事情岔过去了。

今天想起来了，就给补上了。

还是利用上面的例子：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM ALL_OBJECTS;

已创建30947行。

SQL> COMMIT;

提交完成。

SQL> SET AUTOT ON EXP
SQL> CREATE INDEX IND_T_N_NAME ON T(TO_NCHAR(NAME));

索引已创建。

SQL> SELECT * FROM T WHERE NAME = N'T';
SELECT * FROM T WHERE NAME = N'T'
 *
第 1 行出现错误:
ORA-00964: 表名不在 FROM 列表中

下面利用Oracle的EVENTS跟踪一下后台进程，看看到底是哪里报错：

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

会话已更改。

SQL> SELECT * FROM T WHERE NAME = N'T';

        ID NAME
---------- -----
     29502 T
     30798 T
     30829 T
     30925 T
     30932 T

执行计划
----------------------------------------------------------
ERROR:
ORA-00964: 表名不在 FROM 列表中

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

会话已更改。

检查对应的trace文件：

/opt/ora9/admin/testdata/udump/testdata_ora_4437.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /opt/ora9/product/9.2
System name: Linux
Node name: localhost.localdomain
Release: 2.6.9-5.ELsmp
Version: #1 SMP Wed Jan 5 19:30:39 EST 2005
Machine: i686
Instance name: testdata
Redo thread mounted by this instance: 1
Oracle process number: 16
Unix process pid: 4437, image: oracle@localhost.localdomain (TNS V1-V3)

*** 2009-01-16 17:21:16.102
*** SESSION ID:(18.776) 2009-01-16 17:21:16.074
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=69 dep=0 uid=53 ct=42 lid=53 tim=1203220386819182 hv=4111402309 ad='8b9b74fc'
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'
END OF STMT
EXEC #1:c=0,e=45,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1203220386791834
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 26015 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 4910815 p1=1413697536 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=33 dep=0 uid=53 ct=3 lid=53 tim=1203220391769417 hv=1662597695 ad='8b9d067c'
SELECT * FROM T WHERE NAME = N'T'
END OF STMT
PARSE #1:c=0,e=10415,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1203220391769412
BINDS #1:
EXEC #1:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1203220391769546
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
FETCH #1:c=16998,e=17010,p=0,cr=141,cu=0,mis=0,r=1,dep=0,og=4,tim=1203220391786598
WAIT #1: nam='SQL*Net message from client' ela= 666 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
FETCH #1:c=1000,e=795,p=0,cr=6,cu=0,mis=0,r=4,dep=0,og=4,tim=1203220391788127
WAIT #1: nam='SQL*Net message from client' ela= 83051 p1=1413697536 p2=1 p3=0
=====================
PARSING IN CURSOR #2 len=116 dep=1 uid=0 ct=3 lid=0 tim=1203220391871524 hv=431456802 ad='8bf70ffc'
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
END OF STMT
PARSE #2:c=0,e=244,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1203220391871520
BINDS #2:
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 acflg=08 oacfl2=1 size=24 ffset=0
   bfp=b7a1c754 bln=22 avl=04 flg=05
   value=41612
EXEC #2:c=0,e=192,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1203220391871819
FETCH #2:c=1000,e=33,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1203220391871875
STAT #1 id=1 cnt=5 pid=0 pos=1 bj=41612 p='TABLE ACCESS FULL T (cr=147 r=0 w=0 time=17765 us)'
WAIT #0: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 631 p1=1413697536 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=45 dep=0 uid=53 ct=7 lid=53 tim=1203220391872809 hv=1983591574 ad='8b9cf1ec'
DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1
END OF STMT
PARSE #1:c=0,e=147,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1203220391872805
BINDS #1:
 bind 0: dty=1 mxl=32(10) mal=00 scl=00 pre=00 acflg=03 oacfl2=0 size=32 ffset=0
   bfp=b7a0fbb0 bln=32 avl=10 flg=05
   value="PLUS561772"
EXEC #1:c=0,e=291,p=0,cr=4,cu=0,mis=0,r=0,dep=0,og=4,tim=1203220391873162
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 520 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=0 pid=0 pos=1 bj=0 p='DELETE  (cr=4 r=0 w=0 time=52 us)'
=====================
PARSING IN CURSOR #2 len=116 dep=1 uid=0 ct=3 lid=0 tim=1203220391873872 hv=431456802 ad='8bf70ffc'
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
END OF STMT
PARSE #2:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1203220391873869
BINDS #2:
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 acflg=08 oacfl2=1 size=24 ffset=0
   bfp=b7a1c798 bln=22 avl=04 flg=05
   value=36634
EXEC #2:c=0,e=69,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1203220391874032
FETCH #2:c=0,e=33,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1203220391874107
STAT #1 id=2 cnt=0 pid=1 pos=1 bj=36634 p='TABLE ACCESS FULL PLAN_TABLE (cr=4 r=0 w=0 time=49 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 23361 p1=1413697536 p2=1 p3=0
=====================
PARSE ERROR #1:len=81 dep=0 uid=53 ct=3 lid=53 tim=1203220391897725 err=964
EXPLAIN PLAN SET STATEMENT_ID='PLUS561772' FOR SELECT * FROM T WHERE NAME = N'T'
WAIT #1: nam='SQL*Net break/reset to client' ela= 12 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net break/reset to client' ela= 446 p1=1413697536 p2=0 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 35357 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 7639107 p1=1413697536 p2=1 p3=0
=====================
PARSING IN CURSOR #1 len=55 dep=0 uid=53 ct=42 lid=53 tim=1203220399572975 hv=3614818515 ad='8b9ad6fc'
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'
END OF STMT
PARSE #1:c=0,e=106,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1203220399572970
BINDS #1:
EXEC #1:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1203220399573077

根据TRACE文件可以清晰的看到，错误发生在EXPLAIN PLAN语句的时候，莫非是EXPLAIN PLAN语句对NVARCHAR2数据类型的支持存在bug，手工运行一下EXPLAIN PLAN语句：

SQL> SET AUTOT OFF
SQL> EXPLAIN PLAN SET STATEMENT_ID='PLUS561772' FOR SELECT * FROM T WHERE NAME = N'T'
  2  ;
EXPLAIN PLAN SET STATEMENT_ID='PLUS561772' FOR SELECT * FROM T WHERE NAME = N'T'
 *
第 1 行出现错误:
ORA-00964: 表名不在 FROM 列表中

对于不常用的功能和语法，出现问题的机会就比较大，这里显然就是由于EXPLAIN PLAN语句对于NVARCHAR2类型字符串的支持不够，造成了Oracle分析语句的时候报错。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-541300/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-541300/