执行SQL语句遇到3113错误

最近在一家客户那里做数据库健康检查，听用户说有条SQL语句执行时总是报3113错误，经METALINK诊断，确认是BUG 2475995[@more@]

语句为：

select case a.pk_corp when '1014' then '010501'
when '1100' then '292101'
when '1002' then '011401' end as unit_code,
b.code,a.stocks_num,a.stocks_sum from
htnc.Nc_sim_secstockbalance a,
htnc.nc_sim_securities b,
(select pk_corp,pk_securities,max(trade_date) as trade_date
from htnc.Nc_sim_secstockbalance
where pk_corp in ('1014','1100','1002')
and state=0
group by pk_corp,pk_securities) c
where a.pk_securities = c.pk_securities
and a.pk_securities = b.pk_securities
and a.pk_corp = c.pk_corp
and a.trade_date = c.trade_date
and a.pk_corp in ('1014','1100','1002')
and a.state = 0
order by a.pk_corp,b.code

执行该语句后出现的错误为：

ORA-03113: end-of-file on communication channel

警告日志中出现的错误为：

ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [] [] [] [] [] []

解决办法：

1、应用PATCH

2、Put a "no_merge" hint for the view or disable star transformation

修改上面的语句为：

select /*+ NO_MERGE(c) */ case a.pk_corp when '1014' then '010501'
when '1100' then '292101'
when '1002' then '011401' end as unit_code,
b.code,a.stocks_num,a.stocks_sum from
htnc.Nc_sim_secstockbalance a,
htnc.nc_sim_securities b,
(select pk_corp,pk_securities,max(trade_date) as trade_date
from htnc.Nc_sim_secstockbalance
where pk_corp in ('1014','1100','1002')
and state=0
group by pk_corp,pk_securities) c
where a.pk_securities = c.pk_securities
and a.pk_securities = b.pk_securities
and a.pk_corp = c.pk_corp
and a.trade_date = c.trade_date
and a.pk_corp in ('1014','1100','1002')
and a.state = 0
order by a.pk_corp,b.code

再执行，问题解决

如何disable star transformation：

alter system set star_transformation_enabled=false scope=spfile;

重新启动数据库后生效。

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

转载于:http://blog.itpub.net/85922/viewspace-924448/