I am getting an error when selecting the following query on a view with union all in create statement of view.
SQL> select count(*) from v_mon_account_activities ; select count(*) from v_mon_account_activities * ERROR at line 1: ORA-03113: end-of-file on communication channel
But if I run the same select statement as
================================ SQL> select count(*) from v_mon_account_activities where rownum < SQL> 81900; COUNT(*) ---------- 81899 ============================
...it returns the rows. But if I increase the rownum size by one, I get the same error again
=================================== SQL> select count(*) from v_mon_account_activities where rownum < 81901; select count(*) from v_mon_account_activities where rownum < 81901 * ERROR at line 1: ORA-03113: end-of-file on communication channel
1. If I change the union all to union in the iew it works perfectly.
2. The same view works perfectly with other versions (8i).
3. We have now ported the database on Oracle9i where I am getting this problem.
The ORA-3113 error is a general error reported by Oracle client tools, which signifies that they cannot communicate with the oracle shadow process. As it is such a general error more information must be collected to help determine what has happened. There are numerous causes of this error. It is a 'catch all' type error which means 'I cannot communicate with the oracle shadow process'. Check for dump/trace files to see if they can provide you with more information. The fact that you can make it work under certain circumstances (with union vs. union all and on 8i) seems to indicate a 9i bug. Does the table on which the view is built contain a long and is it used in the view? There is a bug 1948420 where long datatypes caused a problem that might be a cause. Other bugs have to do with failover problems such that client sessions that should failover from a network issue (timeout, etc) would generate an 3113 error (bug 2307571).
I'd suggest you look for any trace/dump files and see what further information they provide and then if you continue to have this problem, contact Oracle support.
Dig Deeper on Oracle error messages
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.