ORA-06512: at "SYS.XMLTYPE" 问题记录

来源：互联网发布：表示网站域名的是编辑：程序博客网时间：2024/05/28 11:30

执行SQL报错：

SQL> SELECT COUNT(cl.enable_flg) FROM cont_ledger cl  INNER JOIN project_project pp ON cl.project_cd=pp.org_cd  INNER JOIN res_approve_info rai ON rai.res_approve_info_id=cl.res_approve_id   INNER JOIN res_approve_content rac ON rac.res_approve_info_id=rai.res_approve_info_id  WHERE cl.enable_flg='1'  AND pp.is_virtual='0'  AND cl.created_date>to_date('2016-01-01','yyyy-mm-dd')  AND cl.created_date<to_date('2017-01-01','yyyy-mm-dd')  8     AND EXTRACTVALUE(xmltype(rac.approve_content), '/templet/content/isMonopoly') ='true';   AND EXTRACTVALUE(xmltype(rac.approve_content), '/templet/content/isMonopoly') ='true'                    *ERROR at line 8:ORA-27163: out of memoryORA-06512: at "SYS.XMLTYPE", line 272ORA-06512: at line 1select count(cl.cont_ledger_id) from cont_ledger cl  inner join project_project pp on cl.project_cd=pp.org_cd  inner join res_approve_info rai on rai.res_approve_info_id=cl.res_approve_id   inner join res_approve_content rac on rac.res_approve_info_id=rai.res_approve_info_id  where cl.enable_flg='1'  and pp.is_virtual='0'  and cl.created_date>to_date('2016-01-01','yyyy-mm-dd')  and cl.created_date<to_date('2017-01-01','yyyy-mm-dd')  8     and extractvalue(xmltype(rac.approve_content), '/templet/content/isMonopoly') ='true';   and extractvalue(xmltype(rac.approve_content), '/templet/content/isMonopoly') ='true'                    *ERROR at line 8:ORA-27163: out of memoryORA-06512: at "SYS.XMLTYPE", line 272ORA-06512: at line 1

google到解决办法，说是11.2.0.4的一个小bug，需要设置一个事件才能避免

SQL> ALTER SESSION SET EVENTS '31156 trace name context forever, level 0x400';Session altered.SQL>

OK，可以执行成功了，如下所示：

SQL> select count(cl.cont_ledger_id) from cont_ledger cl  inner join project_project pp on cl.project_cd=pp.org_cd  inner join res_approve_info rai on rai.res_approve_info_id=cl.res_approve_id   inner join res_approve_content rac on rac.res_approve_info_id=rai.res_approve_info_id  where cl.enable_flg='1'  and pp.is_virtual='0'  and cl.created_date>to_date('2016-01-01','yyyy-mm-dd')  and cl.created_date<to_date('2017-01-01','yyyy-mm-dd')  8     and extractvalue(xmltype(rac.approve_content), '/templet/content/isMonopoly') ='true';COUNT(CL.CONT_LEDGER_ID)------------------------            1091SQL>

阅读全文

2 0