Q
Problem solve Get help with specific problems with your technologies, process and projects.

Error when selecting query on a view

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
 

Additional info:
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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close