ora-03113 错误解决方法

环境：windows 2003 32bit 、oracle 9.2.0.1、oracle 9.2.0.8

今天开发公司开发人员在9.2.0.1或9.2.0.8均执行下面sql语句报错:

ora-03113:通信通道文件结束

SQL如下:

[@more@]

(select * from(
(
select obj.*,
priv.applictionId as appId,
role.actionMode,
role.actionIndexs
from g_objectaction role, G_Privilege priv, G_ObjectSet obj
where priv.userid = 'FENGM'
and priv.tag = 0
and role.roleId = priv.roleid
and role.objectid = obj.id)
union(
select obj.*, priv.APPLICTIONID, priv.actionMode, priv.actionIndexs
from G_ObjectSet obj, G_Privilege priv
where priv.userid = 'FENGM'
and priv.tag = 1
and obj.id = priv.roleid)) privTab)
union(
select obj.*,'' as APPLICTIONID,1 as actionMode,0 actionIndexs
from g_objectset obj
where id not in
(

select id from
(
(
select obj.id
from g_objectaction role, G_Privilege priv, G_ObjectSet obj
where priv.userid = 'FENGM'
and priv.tag = 0
and role.roleId = priv.roleid
and role.objectid = obj.id)
union(
select obj.id
from G_ObjectSet obj, G_Privilege priv
where priv.userid = 'FENGM'
and priv.tag = 1
and obj.id = priv.roleid
connect by prior obj.id = obj.parentid
start with obj.parentid = '0'
)
)
tab2
)
)

我们公司一牛人改写该SQL之后，结果集是一样的，就不再报错.

改写SQL如下:

with test as
(
select obj.id
from G_ObjectSet obj, G_Privilege priv
where priv.userid = 'FENGM'
and priv.tag = 1
and obj.id = priv.roleid
connect by prior obj.id = obj.parentid
start with obj.parentid = '0'
union
select obj.id
from g_objectaction role, G_Privilege priv, G_ObjectSet obj
where priv.userid = 'FENGM'
and priv.tag = 0
and role.roleId = priv.roleid
and role.objectid = obj.id
)
(
select * from(
(
select obj.*,
priv.applictionId as appId,
role.actionMode,
role.actionIndexs
from g_objectaction role, G_Privilege priv, G_ObjectSet obj
where priv.userid = 'FENGM'
and priv.tag = 0
and role.roleId = priv.roleid
and role.objectid = obj.id)
union
(
select obj.*, priv.APPLICTIONID, priv.actionMode, priv.actionIndexs
from G_ObjectSet obj, G_Privilege priv
where priv.userid = 'FENGM'
and priv.tag = 1
and obj.id = priv.roleid)
) privTab)
union
(
--select * from test
select obj.*, '' as APPLICTIONID, 1 as actionMode, 0 actionIndexs
from g_objectset obj, test
where obj.id = test.id(+) and test.id is null
)

使用了with字句来改写SQL,去掉了子查询部分，效果等同.

问题解决，特此记录.

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

转载于:http://blog.itpub.net/28227905/viewspace-1060045/