ORA-600(qctVCO:csform)错误

在后台alert文件中，发现了这个错误信息。

 

 

详细错误信息如下：

Errors in file /data/oracle/admin/shandong/udump/shandong_ora_16580.trc:
ORA-00600: internal error code, arguments: [qctVCO:csform], [0], [0], [0], [0], [1], [1], [0]

对应的trace文件内容如下：

[oracle@datasd ~]$ more /data/oracle/admin/shandong/udump/shandong_ora_16580.trc
/data/oracle/admin/shandong/udump/shandong_ora_16580.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /data/oracle/product/10.2
System name:    Linux
Node name:      datasd
Release:        2.6.9-42.0.0.0.1.ELsmp
Version:        #1 SMP Sun Oct 15 15:13:57 PDT 2006
Machine:        x86_64
Instance name: shandong
Redo thread mounted by this instance: 1
Oracle process number: 38
Unix process pid: 16580, image: oracleshandong@datasd

*** ACTION NAME:(SQL 窗口 - 新建) 2009-06-24 22:38:34.505
*** MODULE NAME:(PL/SQL Developer) 2009-06-24 22:38:34.505
*** SERVICE NAME:(shandong) 2009-06-24 22:38:34.505
*** SESSION ID:(62.10898) 2009-06-24 22:38:34.505
*** 2009-06-24 22:38:34.505
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [qctVCO:csform], [0], [0], [0], [0], [1], [1], [0]
Current SQL statement for this session:
update inf_haocai_property w set w.retail_price=(select q.建议零售限价
  from sdzzhc_saler_wfbe q where q.序号 in( select f.xuhao from
inf_haocai_product f,
  inf_haocai_property e where f.product_id=e.product_id
  and e.plat_id=59 and f.factory_id in ('110472', '111126') and f.xuhao>=100118)) where w.plat_id=59
----- Call Stack Trace -----
calling              call     entry                argument values in hex     
location             type     point                (? means dubious value)    
-------------------- -------- -------------------- ----------------------------
ksedst()+31          call     ksedst1()            000000000 ? 000000001 ?
                                                   7FBFFF6800 ? 7FBFFF6860 ?
                                                   7FBFFF67A0 ? 000000000 ?
ksedmp()+610         call     ksedst()             000000000 ? 000000001 ?
                                                   7FBFFF6800 ? 7FBFFF6860 ?
                                                   7FBFFF67A0 ? 000000000 ?
ksfdmp()+21          call     ksedmp()             000000003 ? 000000001 ?
                                                   7FBFFF6800 ? 7FBFFF6860 ?
                                                   7FBFFF67A0 ? 000000000 ?
kgerinv()+161        call     ksfdmp()             000000003 ? 000000001 ?
                                                   7FBFFF6800 ? 7FBFFF6860 ?
                                                   7FBFFF67A0 ? 000000000 ?
kgeasnmierr()+163    call     kgerinv()            006618E20 ? 2A971E11F8 ?
                                                   7FBFFF6860 ? 7FBFFF67A0 ?
                                                   000000000 ? 000000000 ?
qctValidCharOpn()+6  call     kgeasnmierr()        006618E20 ? 2A971E11F8 ?
55                                                 7FBFFF6860 ? 7FBFFF67A0 ?

查询了一下metalink，发现在文档中Doc ID:  5603197.8描述了这个问题，问题和UPDATE语句有关，当UPDATE语句包含了子查询，由于CBO尝试MERGE VIEW导致了这个错误的产生，在10.2.0.4和11.1.0.6中解决了这个问题。

SQL> explain plan for
  2  update inf_haocai_property w
  3  set w.retail_price=
  4     (
  5             select q.建议零售限价
  6             from sdzzhc_saler_wfbe q
  7             where q.序号 in
  8                     (
  9                             select f.xuhao
 10                             from inf_haocai_product f, inf_haocai_property e
 11                             where f.product_id=e.product_id 
 12                             and e.plat_id=59
 13                             and f.factory_id in ('110472', '111126')
 14                             and f.xuhao>=100118
 15                     )
 16     )
 17  where w.plat_id=59;
update inf_haocai_property w
       *
ERROR at line 2:
ORA-00600: internal error code, arguments: [qctVCO:csform], [0], [0], [0], [0], [1], [1], [0]

 

SQL> alter session set "_complex_view_merging" = false;

Session altered.

SQL> explain plan for
  2  update inf_haocai_property w
  3  set w.retail_price=
  4     (
  5             select q.建议零售限价
  6             from sdzzhc_saler_wfbe q
  7             where q.序号 in
  8                     (
  9                             select f.xuhao
 10                             from inf_haocai_product f, inf_haocai_property e
 11                             where f.product_id=e.product_id 
 12                             and e.plat_id=59
 13                             and f.factory_id in ('110472', '111126')
 14                             and f.xuhao>=100118
 15                     )
 16     )
 17  where w.plat_id=59;

Explained.

SQL> set pages 100 lines 120
SQL> col plan_plus_exp format a100
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 1970010803

-------------------------------------------------------------------------------------------
| Id | Operation              | Name                | Rows  | Bytes | Cost (%CPU)|Time    |
-------------------------------------------------------------------------------------------
|  0 | UPDATE STATEMENT       |                     | 10925 |   170K|   144   (0)|00:00:03|
|  1 |  UPDATE                | INF_HAOCAI_PROPERTY |       |       |            |        |
|* 2 |   TABLE ACCESS FULL    | INF_HAOCAI_PROPERTY | 10925 |   170K|   144   (0)|00:00:03|
|  3 |   NESTED LOOPS         |                     |     1 |   117 |   252   (1)|00:00:04|
|  4 |    VIEW                | VW_NSO_1            |     1 |    13 |   248   (1)|00:00:04|
|  5 |     SORT UNIQUE        |                     |     1 |    23 |            |        |
|* 6 |      HASH JOIN         |                     |     1 |    23 |   248   (1)|00:00:04|
|* 7 |       TABLE ACCESS FULL| INF_HAOCAI_PRODUCT  |     1 |    15 |   103   (0)|00:00:02|
|* 8 |       TABLE ACCESS FULL| INF_HAOCAI_PROPERTY | 10925 | 87400 |   144   (0)|00:00:03|
|* 9 |    TABLE ACCESS FULL   | SDZZHC_SALER_WFBE   |     1 |   104 |     3   (0)|00:00:01|
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("W"."PLAT_ID"=59)
   6 - access("F"."PRODUCT_ID"="E"."PRODUCT_ID")
   7 - filter("F"."XUHAO">=100118 AND ("F"."FACTORY_ID"=110472 OR
              "F"."FACTORY_ID"=111126))
   8 - filter("E"."PLAT_ID"=59)
   9 - filter("$nso_col_1"=TO_NUMBER("Q"."序号"))

Note
-----
   - dynamic sampling used for this statement

30 rows selected.

将隐含参数_complex_view_merging设置为false，可以避免这个错误的产生。

 

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

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