一次ORA-03113错误解决

sjk2013

浏览: 2185613 次

最近访客更多访客>>

BDQN磊

w11h22j33

devcang

hedgehog12

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (3465)

社区版块

存档分类

2014-05 ( 13)
2014-04 ( 74)
2014-03 ( 72)
更多存档...

原始SQL:

执行出现ORA-03113错误,网络正常,执行其他SQL也正常.

13:04:26 CRY@CRY> select operatetime,billtypename,billid,shopcode,shopname,goodscode,goodsn
ame,' ' 期初数量,sum(irealnumber) irealnumber,DECODE(sum(irealnumber),0,0,round(sum(imoney)/sum(irea
lnumber),2)) iprice,sum(imoney) imoney,sum(orealnumber) orealnumber,DECODE(sum(orealnumber),0,0,roun
d(sum(omoney)/sum(orealnumber),2)) oprice,sum(omoney) omoney,' ' 结存数量 from( select to_char(x.ope
ratetime, 'YYYY-MM-DD') operatetime,x.billtype,
13:04:28   2         fun_getname('BILLTYPEALL',x.billtype) billtypename,
13:04:28   3         x.billid,x.remark,x.originbillid,
13:04:28   4         fun_getname('OPERATIONTYPE',x.otherinouttype) otherinouttypename,
13:04:28   5         x.shopcode,
13:04:28   6         f_getOrganiseName(x.shopcode) shopname,
13:04:28   7         x.storecode,
13:04:28   8         f_getStoreName(x.storecode) storename,
13:04:28   9          FUN_GETTOUNIT(x.billtype,x.billid) unitname,
13:04:28  10         x.goodscode,
13:04:28  11         X.goodsname,
13:04:28  12         x.ShortCode,x.brandcode,x.model,
13:04:28  13         x.irealnumber,
13:04:28  14         abs(DECODE(x.irealnumber,0,0,round(x.imoney/x.irealnumber,4))) iprice,
13:04:28  15         x.imoney,x.orealnumber,
13:04:28  16         abs(DECODE(x.orealnumber,0,0,round(x.omoney/x.orealnumber,4))) oprice,
13:04:28  17         x.omoney
13:04:28  18  from (
13:04:28  19  select a.operatetime,
13:04:28  20                 a.billtype,
13:04:28  21                 a.billid,fun_getbillremark(a.billid) remark,fun_getbilloriginbillid(a.b
illid) originbillid,
13:04:28  22                 a.otherinouttype,
13:04:28  23                 a.shopcode,
13:04:28  24                 a.storecode,
13:04:28  25                 a.goodscode,
13:04:28  26                 c.shortcode,c.brandcode,c.model,
13:04:28  27                 c.goodsname,
13:04:28  28                 sum(DECODE(b.ruleid,1,a.realnumber,2,-a.realnumber,0)) irealnumber,
13:04:28  29                 sum(DECODE(b.ruleid,1,
13:04:28  30                        round(a.realnumber * a.price,2),
13:04:28  31                       2,
13:04:28  32                        round(-1 * a.realnumber *a.price,2),
13:04:28  33                     0)) imoney,
13:04:28  34                 sum(DECODE(b.ruleid,2,a.realnumber,1,-a.realnumber,0)) orealnumber,
13:04:28  35                 sum(decode(b.ruleid,2,
13:04:28  36                        round(a.realnumber * a.price,2),
13:04:28  37                       1,
13:04:28  38                        round(-1 * a.realnumber * a.price,2),
13:04:28  39                     0)) omoney
13:04:28  40            from TAB_A a, TAB_B b,TAB_C c
13:04:28  41           where a.billtype = b.billtype
13:04:28  42               and a.goodscode=c.goodscode  and (a.shopcode = '0603')
13:04:28  43           group by a.operatetime,
13:04:28  44                    a.billtype,
13:04:28  45                    a.billid,
13:04:28  46                    remark,
13:04:28  47                    a.otherinouttype,
13:04:28  48                    a.shopcode,
13:04:28  49                    a.storecode,
13:04:28  50                    a.goodscode,
13:04:28  51                    c.shortcode,
13:04:28  52                    c.goodsname,c.brandcode,c.model
13:04:28  53  ) x
13:04:28  54  where x.billtype in (select billtype from t_billlist where ruleid>0) and
13:04:28  55     x.operatetime >= to_date('2013-6-17','YYYY-MM-DD')
13:04:28  56     and x.operatetime < to_date('2013-6-18','YYYY-MM-DD')
13:04:28  57  ) a  group by operatetime,billtypename,billid,shopcode,shopname,goodscode,goodsname or
der by operatetime,billtypename,billid,shopcode,shopname,goodscode,goodsname;
select operatetime,billtypename,billid,shopcode,shopname,goodscode,goodsname,' ' 期初数量,sum(irealn
umber) irealnumber,DECODE(sum(irealnumber),0,0,round(sum(imoney)/sum(irealnumber),2)) iprice,sum(imo
ney) imoney,sum(orealnumber) orealnumber,DECODE(sum(orealnumber),0,0,round(sum(omoney)/sum(orealnumb
er),2)) oprice,sum(omoney) omoney,' ' 结存数量 from( select to_char(x.operatetime, 'YYYY-MM-DD') ope
ratetime,x.billtype,
*
第 1 行出现错误:
ORA-03113: 通信通道的文件结尾
进程 ID: 20027
会话 ID: 201 序列号: 149


ERROR:
ORA-03114: 未连接到 ORACLE


已用时间:  00: 00: 02.01

精简改SQL如下后报ORA-600错误:

13:07:18 CRY@CRY> SELECT operatetime
13:07:19   2    FROM (SELECT to_char(x.operatetime, 'YYYY-MM-DD') operatetime
13:07:19   3            FROM (SELECT operatetime FROM t_storeout GROUP BY operatetime) x
13:07:19   4           WHERE x.operatetime > to_date('2012-01-01', 'YYYY-MM-DD')) a
13:07:19   5   GROUP BY operatetime;
          FROM (SELECT operatetime FROM t_storeout GROUP BY operatetime) x
                                        *
第 3 行出现错误:
ORA-00600: 内部错误代码, 参数: [rwoirw: check ret val], [], [], [], [], [], [], [], [], [], [], []


已用时间:  00: 00: 01.68

分析此ORA-00600错误,发现是参数"_complex_view_merging"的影响,将此参数值设为FALSE后正常.

如是解决此ORA-00600错误后,此ORA-03113错误也就解决了,如下:

13:07:23 CRY@CRY> ALTER SESSION SET "_complex_view_merging"=false;

会话已更改。

已用时间:  00: 00: 00.01
13:08:43 CRY@CRY> select operatetime,billtypename,billid,shopcode,shopname,goodscode,goodsn
ame,' ' 期初数量,sum(irealnumber) irealnumber,DECODE(sum(irealnumber),0,0,round(sum(imoney)/sum(irea
lnumber),2)) iprice,sum(imoney) imoney,sum(orealnumber) orealnumber,DECODE(sum(orealnumber),0,0,roun
d(sum(omoney)/sum(orealnumber),2)) oprice,sum(omoney) omoney,' ' 结存数量 from( select to_char(x.ope
ratetime, 'YYYY-MM-DD') operatetime,x.billtype,
13:08:55   2         fun_getname('BILLTYPEALL',x.billtype) billtypename,
13:08:55   3         x.billid,x.remark,x.originbillid,
13:08:55   4         fun_getname('OPERATIONTYPE',x.otherinouttype) otherinouttypename,
13:08:55   5         x.shopcode,
13:08:55   6         f_getOrganiseName(x.shopcode) shopname,
13:08:55   7         x.storecode,
13:08:55   8         f_getStoreName(x.storecode) storename,
13:08:55   9          FUN_GETTOUNIT(x.billtype,x.billid) unitname,
13:08:55  10         x.goodscode,
13:08:55  11         X.goodsname,
13:08:55  12         x.ShortCode,x.brandcode,x.model,
13:08:55  13         x.irealnumber,
13:08:55  14         abs(DECODE(x.irealnumber,0,0,round(x.imoney/x.irealnumber,4))) iprice,
13:08:55  15         x.imoney,x.orealnumber,
13:08:55  16         abs(DECODE(x.orealnumber,0,0,round(x.omoney/x.orealnumber,4))) oprice,
13:08:55  17         x.omoney
13:08:55  18  from (
13:08:55  19  select a.operatetime,
13:08:55  20                 a.billtype,
13:08:55  21                 a.billid,fun_getbillremark(a.billid) remark,fun_getbilloriginbillid(a.b
illid) originbillid,
13:08:55  22                 a.otherinouttype,
13:08:55  23                 a.shopcode,
13:08:55  24                 a.storecode,
13:08:55  25                 a.goodscode,
13:08:55  26                 c.shortcode,c.brandcode,c.model,
13:08:55  27                 c.goodsname,
13:08:55  28                 sum(DECODE(b.ruleid,1,a.realnumber,2,-a.realnumber,0)) irealnumber,
13:08:55  29                 sum(DECODE(b.ruleid,1,
13:08:55  30                        round(a.realnumber * a.price,2),
13:08:55  31                       2,
13:08:55  32                        round(-1 * a.realnumber *a.price,2),
13:08:55  33                     0)) imoney,
13:08:55  34                 sum(DECODE(b.ruleid,2,a.realnumber,1,-a.realnumber,0)) orealnumber,
13:08:55  35                 sum(decode(b.ruleid,2,
13:08:55  36                        round(a.realnumber * a.price,2),
13:08:55  37                       1,
13:08:55  38                        round(-1 * a.realnumber * a.price,2),
13:08:55  39                     0)) omoney
13:08:55  40            from t_storeout a, t_billlist b,t_goods c
13:08:55  41           where a.billtype = b.billtype
13:08:55  42               and a.goodscode=c.goodscode  and (a.shopcode = '0603')
13:08:55  43           group by a.operatetime,
13:08:55  44                    a.billtype,
13:08:55  45                    a.billid,
13:08:55  46                    remark,
13:08:55  47                    a.otherinouttype,
13:08:55  48                    a.shopcode,
13:08:55  49                    a.storecode,
13:08:55  50                    a.goodscode,
13:08:55  51                    c.shortcode,
13:08:55  52                    c.goodsname,c.brandcode,c.model
13:08:55  53  ) x
13:08:55  54  where x.billtype in (select billtype from t_billlist where ruleid>0) and
13:08:55  55     x.operatetime >= to_date('2013-6-17','YYYY-MM-DD')
13:08:55  56     and x.operatetime < to_date('2013-6-18','YYYY-MM-DD')
13:08:55  57  ) a  group by operatetime,billtypename,billid,shopcode,shopname,goodscode,goodsname or
der by operatetime,billtypename,billid,shopcode,shopname,goodscode,goodsname;

未选定行

已用时间:  00: 00: 00.04

分享到：