oracle 10g 和11g的差异真的有这么大？？

Question

刚刚把系统从10g升级到11g，安装配置哪些流程大同小异，但是第一次登陆就应为11g这个区分大小写给弄迷糊了。更改参数到不区分之后，数据完全导入之后，大体运行正常。但是有些查询怎么也过不去，就是提示ora-03113 end of file for communication of channel\ora-03114这两个错误。回想一下，在10g中执行正常的语句怎么到这就不行了，跟踪才知道：
10g中用到大量的with as完成查询，因为查询太复杂，用这个方便。可是问题就来了。在11g里面简单的with as 可以正常执行，但是复杂的就不行了。比如针对临时视图进行多次分组、连接就会报错，如果只是简单的多次连接就没事
with tmpa as (select a,b,c from XXX2),
     tmpb as (select e as a,r as b,t as c from XXX1)
select a,b,c from tmpa 
 union all 
select a,b,c from tmpa
 union all
select a,b,c from tmpb
 order by a;
这样是可以的，因为终的查询没有对tmpa、tmpb进行更复杂的分组操作
with tmpa as (select a,b,c from XXX2),
     tmpb as (select e as a,r as b,t as c from XXX1)
select a,b,c from tmpa 
 union all 
select a,count(b) as b,sum(c) as c from tmpa group by a
 union all
select a,b,c from tmpb
 order by a;
这样就不行了
总结一点：对with as 产生的临时视图进行多于一次的复杂查询（如分组）就会出现这个错误。
我现在只是在多次用到tmpa的地方用其他的名字替代了，就是同样地tmpa而已。谁能有比较好的办法，是不是数据库参数的问题？可以在线讨论。

Answer 1

with as 简要可以理解为“中间表、虚拟视图、临时视图”，姑且就叫临时视图吧，总之它是一个不存在的对象，查询语句被执行时才能被“看到”。那么是不是再一个查询语句当中，这个“临时视图”只能以一种方式存在呢？？？进行测试如下：
with tmpa as (select a,b,c from XXX2),
  tmpb as (select e as a,r as b,t as c from XXX1)
select a,b,c from tmpa  
 union all  
select a,b,c from tmpa
 union all
select a,b,c from tmpb
 order by a;
上面红色显示的查询无论被连接多少次，结果都是正确的，主查询语句要递归到临时视图上面，而且他们都是一个状态。
假如这样
with tmpa as (select a,b,c from XXX2),
  tmpb as (select e as a,r as b,t as c from XXX1)
select a,b,c from tmpa  
 union all  
select a,count(b) as b,sum(c) as c from tmpa group by a
union all  
select count(a) as a,b,sum(c) as c from tmpa group by b
 union all
select a,b,c from tmpb
 order by a;
这样就不行了，我才对tmpa进行分组在查询的时候，已经改变的最早with as 中对tmpa的定义，
因为在with as中多加入几个相同结构的tmpa，起不同的别名就可以了。这样虽然解决问题，但是麻烦得很  语句都要重新调试，总觉得还是能修改数据库的参数来去掉这个限制。10g都能行的，怎么到这就不行了呢？？？
来几个大虾呗

Answer 2

不是脚本的问题
是不是 group by 导致某些空间不足造成的啊 
等高手

Answer 3

with tmpa as (select a,b,c from XXX2),
  tmpb as (select e as a,r as b,t as c from XXX1)
select a,b,c from tmpa  
 union all  
select a,count(b) as b,sum(c) as c from tmpa group by a
 union all
select a,b,c from tmpb
 order by a;上面这样查询，你修改了查询字段的结构，count(b)是数字类型，但是 b字段可能不是数字类型的吧，
只要数据类型一致，就可以实现上面的查询

Answer 4

应该不是类型的问题，楼上说的空间倒有可能。那个脚本只是我根据自己的脚本做的范例，只要对tmpa重新分组在union 就会爆出ora-03113错误。如果不充分分组，或是另起一个别名的话就没事了，很奇怪

Answer 5

这些脚本都是从10g上copy过来的  ，到11g上就报错，所以我觉得很有可能是设置的事儿

Answer 6

问题基本确定了，看一下这两种写法
一、
with tmpa as (select a,b,c from XXX2),
  tmpb as (select e as a,r as b,t as c from XXX1)
select a,b,c from tmpa   
 union all   
select a,count(b) as b,count(c) as c from tmpa group by a
union all   
select count(a) as a,b,count(c) as c from tmpa group by b
 union all
select a,b,c from tmpb
 order by a;
二、
with tmpa as (select a,b,c from XXX2),
  tmpb as (select e as a,r as b,t as c from XXX1)
select a,b,c from tmpa   
 union all   
select a,count(b) as b,count(distinct c) as c from tmpa group by a
union all   
select count(a) as a,b,count(distinct c ) as c from tmpa group by b
 union all
select a,b,c from tmpb
 order by a;第一种写法正确执行，第二种就出错，distinct 出现一次可以 出现两次以上就是 ora-03113了
十分不解

Answer 7

看看是不是这个问题？http://stackoverflow.com/questions/4364459/query-works-with-oracle-10g-but-not-with-11gif you have access to support, it looks like Bug 9478304: LOOP FAILING WITH ORA-00979: NOT A GROUP BY EXPRESSION. This seems to affect 11.2.0.1 only.