mos 上例举此bug 是当树在作为子查询的时候，父查询使用了 on ansci语法字句，导致报错 ora-00976
再次显示bug如下：
| SQL> select * from v$version where rownum=1; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production |
| SQL> create table level_test (col3 number(38), col4 number(38)); Table created. SQL> alter session set events '976 trace name errorstack forever, level 3'; Session altered. SQL> SELECT * 2 FROM (select 1 col1 from dual) 3 JOIN (select 1 col2 from dual) 4 ON col1 = col2 5 AND col2 in (SELECT col3 6 FROM level_test 7 WHERE LEVEL IN (1, 2) 8 START WITH col4 = 0 9 CONNECT BY col4 = PRIOR col3); WHERE LEVEL IN (1, 2) * ERROR at line 7: ORA-00976: Specified pseudocolumn or operator not allowed here. |
| [oracle@HQ-RD-COREDB trace]$ more midcore_ora_11846.trc Trace file /u01/app/oracle/diag/rdbms/midcore/midcore/trace/midcore_ora_11846.trc Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 System name: Linux Node name: HQ-RD-COREDB Release: 2.6.32-220.el6.x86_64 Version: #1 SMP Wed Nov 9 08:03:13 EST 2011 Machine: x86_64 Instance name: midcore Redo thread mounted by this instance: 1 Oracle process number: 35 Unix process pid: 11846, image: oracle@HQ-RD-COREDB (TNS V1-V3) *** 2014-05-21 16:35:45.072 *** SESSION ID:(301.1407) 2014-05-21 16:35:45.072 *** CLIENT ID:() 2014-05-21 16:35:45.072 *** SERVICE NAME:(SYS$USERS) 2014-05-21 16:35:45.072 *** MODULE NAME:(SQL*Plus) 2014-05-21 16:35:45.072 *** ACTION NAME:() 2014-05-21 16:35:45.072 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0) ----- Error Stack Dump ----- ORA-00976: Specified pseudocolumn or operator not allowed here. ----- Current SQL Statement for this session (sql_id=8snqbaqgd5ays) ----- SELECT * FROM (select 1 col1 from dual) JOIN (select 1 col2 from dual) ON col1 = col2 AND col2 in (SELECT col3 FROM level_test WHERE LEVEL IN (1, 2) START WITH col4 = 0 CONNECT BY col4 = PRIOR col3) ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- skdstdst()+36 call kgdsdst() 000000000 ? 000000000 ? 7FFFE7A9F418 ? 000000001 ? 000000001 ? 000000002 ? ksedst1()+98 call skdstdst() 000000000 ? 000000000 ? 7FFFE7A9F418 ? 000000001 ? 000000000 ? 000000002 ? ksedst()+34 call ksedst1() 000000000 ? 000000001 ? 7FFFE7A9F418 ? 000000001 ? 000000000 ? 000000002 ? dbkedDefDump()+2741 call ksedst() 000000000 ? 000000001 ? 7FFFE7A9F418 ? 000000001 ? 000000000 ? 000000002 ? ksedmp()+36 call dbkedDefDump() 000000003 ? 000000000 ? 7FFFE7A9F418 ? 000000001 ? 000000000 ? 000000002 ? dbkdaKsdActDriver() call ksedmp() 000000003 ? 000000000 ? +1960 7FFFE7A9F418 ? 000000001 ? 000000000 ? 000000002 ? dbgdaExecuteAction( call dbkdaKsdActDriver() 7F8A0A1F8710 ? 7FFFE7AA60A0 ? )+1065 7FFFE7A9F418 ? 000000001 ? 000000000 ? 000000002 ? dbgdaRunAction()+81 call dbgdaExecuteAction( 7F8A0A1F8710 ? 00A0A83E0 ? 0 ) 0020C0003 ? 7FFFE7AA60A0 ? 000000001 ? 000000002 ? dbgdRunActions()+59 call dbgdaRunAction() 7F8A0A1F8710 ? 000000005 ? 0020C0003 ? 7FFFE7AA60A0 ? 000000001 ? 000000002 ? dbgdProcessEventAct call dbgdRunActions() 7F8A0A1F8710 ? 000000005 ? ions()+651 0020C0003 ? 7FFFE7AA60A0 ? 000000001 ? 000000002 ? dbgdChkEventKgErr() call dbgdProcessEventAct 7F8A0A1F8710 ? 00BAF3FA0 ? +1653 ions() 7F8A09C2F660 ? 7FFFE7AA60A0 ? 000000001 ? 000000002 ? dbkdChkEventRdbmsEr call dbgdChkEventKgErr() 7F8A0A1F8710 ? 00BAF3FA0 ? r()+56 7FFFE7A9BC50 ? 7FFFE7AA60A0 ? 000000001 ? 000000002 ? ksfpec()+61 call dbkdChkEventRdbmsEr 7FFFE7A9BC50 ? 00BAF3FA0 ? r() 7FFFE7A9BC50 ? 7FFFE7AA60A0 ? 000000001 ? 000000002 ? dbgePostErrorKGE()+ call ksfpec() 7FFFE7A9BC50 ? 7FFFE7A9BC50 ? 1129 7FFFE7A9BC50 ? 7FFFE7AA60A0 ? 000000001 ? 000000002 ? dbkePostKGE_kgsf()+ call dbgePostErrorKGE() 00BAF3FA0 ? 7F8A09CB0040 ? 66 0000003D0 ? 000000000 ? 100000000 ? 000000002 ? kgeselv()+276 call dbkePostKGE_kgsf() 00BAF3FA0 ? 7F8A09CB0040 ? 0000003D0 ? 000000000 ? 100000000 ? 000000002 ? kgesecl0()+134 call kgeselv() 00BAF3FA0 ? 7F8A09CB0040 ? 0000003D0 ? 000000000 ? 100000000 ? 000000000 ? qcuErroer()+37 call kgesecl0() 00BAF3FA0 ? 7F8A09CB0040 ? 0000003D0 ? 000000000 ? 100000000 ? 009511150 ? qcuErroep()+47 call qcuErroer() 00BAF3FA0 ? 7F8A09CB0040 ? 100000000 ? 000000000 ? 100000000 ? 009511150 ? qcpiapr()+3303 call qcuErroep() 00BAF3FA0 ? 000000000 ? 100000000 ? 0000003D0 ? 100000000 ? 009511150 ? |
打补丁：13496250，未设置event报错依旧。
| [oracle@HQ-RD-COREDB 13496250]$ opatch lsinventory Oracle Interim Patch Installer version 11.2.0.3.6 Copyright (c) 2013, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0/db_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc OPatch version : 11.2.0.3.6 OUI version : 11.2.0.3.0 Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-05-22_11-36-05AM_1.log Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-05-22_11-36-05AM.txt -------------------------------------------------------------------------------- Installed Top-level Products (1): Oracle Database 11g 11.2.0.3.0 There are 1 product(s) installed in this Oracle Home. Interim patches (1) : Patch 13496250 : applied on Thu May 22 11:35:35 CST 2014 Unique Patch ID: 14378520 Created on 16 Dec 2011, 17:43:45 hrs PST8PDT Bugs fixed: 13496250 -------------------------------------------------------------------------------- OPatch succeeded. [oracle@HQ-RD-COREDB 13496250]$ [oracle@HQ-RD-COREDB 13496250]$ sqlplus '/as sysdba'; SQL*Plus: Release 11.2.0.3.0 Production on Thu May 22 11:39:45 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup; ORACLE instance started. Total System Global Area 8134017024 bytes Fixed Size 2244472 bytes Variable Size 1291845768 bytes Database Buffers 6610223104 bytes Redo Buffers 229703680 bytes Database mounted. Database opened. SQL> conn test/test Connected. SQL> SELECT * 2 FROM (select 1 col1 from dual) 3 JOIN (select 1 col2 from dual) 4 ON col1 = col2 5 AND col2 in (SELECT col3 6 FROM level_test 7 WHERE LEVEL IN (1, 2) 8 START WITH col4 = 0 9 CONNECT BY col4 = PRIOR col3); WHERE LEVEL IN (1, 2) * ERROR at line 7: ORA-00976: Specified pseudocolumn or operator not allowed here. |
设置13496250 trace，问题不在有。
| oracle@HQ-RD-COREDB 13496250]$ sqlplus 'test/test'; SQL*Plus: Release 11.2.0.3.0 Production on Thu May 22 11:45:25 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter session set events '13496250 trace name context forever, level 1'; Session altered. SQL> SELECT * 2 FROM (select 1 col1 from dual) 3 JOIN (select 1 col2 from dual) 4 ON col1 = col2 5 AND col2 in (SELECT col3 6 FROM level_test 7 WHERE LEVEL IN (1, 2) 8 START WITH col4 = 0 9 CONNECT BY col4 = PRIOR col3); no rows selected SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@HQ-RD-COREDB 13496250]$ [oracle@HQ-RD-COREDB 13496250]$ sqlplus '/as sysdba > '; SQL*Plus: Release 11.2.0.3.0 Production on Thu May 22 11:47:19 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter system set events '13496250 trace name context forever, level 1'; System altered. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@HQ-RD-COREDB 13496250]$ [oracle@HQ-RD-COREDB 13496250]$ sqlplus '/as sysdba'; SQL*Plus: Release 11.2.0.3.0 Production on Thu May 22 11:47:43 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn test/test Connected. SQL> SELECT * 2 FROM (select 1 col1 from dual) 3 JOIN (select 1 col2 from dual) 4 ON col1 = col2 5 AND col2 in (SELECT col3 6 FROM level_test 7 WHERE LEVEL IN (1, 2) 8 START WITH col4 = 0 9 CONNECT BY col4 = PRIOR col3); no rows selected SQL> |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30182853/viewspace-1479134/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30182853/viewspace-1479134/
扫一扫
1041
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
