MySQL

做最专业的DBA

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

执行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 memory
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1


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';
   and extractvalue(xmltype(rac.approve_content), '/templet/content/isMonopoly') ='true'
                    *
ERROR at line 8:
ORA-27163: out of memory
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-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)
------------------------
            1091

SQL> 
阅读更多

扫码向博主提问

去开通我的Chat快问

mchdba

博客专家

非学,无以致疑;非问,无以广识
个人分类: - - - MySQL Install
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

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

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭