oracle 中union all两张数据量大的表,报ORA-03113:通信通道的文件结束.具体语句:
SELECT aday, sum(sl) dhs, sum(ds) ddds
FROM (Select *
From xx.VIEW_TABLE1
WHERE to_date(aday, 'yyyyMMdd') >=
to_date('2009-09-01', 'yyyy-MM-dd')
AND to_date(aday, 'yyyyMMdd') <=
to_date('2010-01-13', 'yyyy-MM-dd')
union all
Select *
From yy.VIEW_TABLE2
WHERE to_date(aday, 'yyyyMMdd') >=
to_date('2009-09-01', 'yyyy-MM-dd')
AND to_date(aday, 'yyyyMMdd') <=
to_date('2010-01-13', 'yyyy-MM-dd'))
GROUP BY logday --xx,yy是用户名。单个测试没错,union all时报的错。
SELECT aday, sum(sl) dhs, sum(ds) ddds
FROM (Select *
From xx.VIEW_TABLE1
WHERE to_date(aday, 'yyyyMMdd') >=
to_date('2009-09-01', 'yyyy-MM-dd')
AND to_date(aday, 'yyyyMMdd') <=
to_date('2010-01-13', 'yyyy-MM-dd')
union all
Select *
From yy.VIEW_TABLE2
WHERE to_date(aday, 'yyyyMMdd') >=
to_date('2009-09-01', 'yyyy-MM-dd')
AND to_date(aday, 'yyyyMMdd') <=
to_date('2010-01-13', 'yyyy-MM-dd'))
GROUP BY logday --xx,yy是用户名。单个测试没错,union all时报的错。
FROM (Select aday,sl,ds
From xx.VIEW_TABLE1
WHERE to_date(aday, 'yyyyMMdd') >=
to_date('2009-09-01', 'yyyy-MM-dd')
AND to_date(aday, 'yyyyMMdd') <=
to_date('2010-01-13', 'yyyy-MM-dd')
union all
Select aday,sl,ds
From yy.VIEW_TABLE2
WHERE to_date(aday, 'yyyyMMdd') >=
to_date('2009-09-01', 'yyyy-MM-dd')
AND to_date(aday, 'yyyyMMdd') <=
to_date('2010-01-13', 'yyyy-MM-dd')) t
GROUP BY t.aday
http://www.dbanotes.net/Oracle/ORA-03113.htm