what is ora-03113 ??

hi all,

i am getting ora-03113:end of file on communication channel , after running a pl/sql procedure which creates number of tables/indexes all through dynamic queries(execute immediate).
I am running this pl/sql procedure from my own user and all the new tables are getting created properly but at some point ora-03113 is getting generated and the connection is terminated by oracle after this error. If i dont run this procedure then everything is working fine and my other procedures are running fine too. The only difference between this failing procedure and others is that i am using lots of "execute immediate" statements and DBMS_SQL.EXECUTE(v_cursorid) command to dynamically create tables.

The oracle server has recently been shifted from 8i to 9i and i am getting this error only after running this procedures on the new 9i server(standard edition). The same procedure used to run fine in 8i.

Pls suggest what's going wrong and how to solve this problem.

thanks

Parijat Paul

I have received same error message, I got this error, whenever i try to drop, create, alter the tables. All the DML operations are working properly.

I have also need of help.

Namet ullah kalair

Hi,


Thsi probs due to insufficent Shared pool area to execute your program
Plz try this by flshing shared poll
sql>alter system flush shared_pool;


It May help U



Tks
Gangu

Hmmm Not sure if this has anything to do with shared pool as you would expect another error message (Oracle's good like that).

Most likely network connection has dropped.
Can be many causes:

Check Alert Log for sever errors.
Problem with Listener?
Protocol error (TNSNAMES).
Network Error?

To Name a few.

I've gotten dropped connections for annoying Oracle so many times now it no longer bothers me. I open another connection, try the same act again, and if it blows up again, I know I've hit a roadblock. I generally put it in reverse, turn the wheel a little and try to hit it from a different direction.

I would simply suggest breaking up your script to try to find exactly which command is annoying Oracle. Then see if you can do what you are attempting in a different way. Not very scientific, but probably the quickest way around the problem.

- Chris

hi all,

I could debug the entire pl/sql procedure and found that the
following delete statement is causing the oracle error: ora-03113.
---------------
delete FROM PROPERTIES_TEXT_LARGE a WHERE exists
(SELECT b.valueobjecttypeid, B.valueobjectid FROM PROPERTIES_OBJECTS b WHERE b.objecttypeid = 10217 AND b.valueobjecttypeid > 40000
and a.objecttypeid = b.valueobjecttypeid and a.objectid = b.valueobjectid);
---------------

This delete statement is a part of bulk deletes and almost 25-30 similar delete statements form the entire transaction. So if any of the delete stmts fails then the entire transaction rolls back. I was able to run the bulk deletes after commenting out this delete stmt. The unique property of this statement is that it has a field of type varchar2(4000) and at a given time we are deleting say only 20 records from this table. It seems there is some problem with the joining of the two tables in the delete stmt. If i delete from the same table without the join then it works fine but i cant do that all the time. I was using an "in" stmt inplace of the "where exists" but even after changing the "in" with "exists" the error still occurs.

pls send your views...

thanks for all ur earlier responses!

Parijat Paul

my mom always says when I hit 3113 I should check my udump and look the trace file and most of times I find ORA-00600 and ORA-07445 in the file :D

Vote for pando's mom as moderator !

Quote:

---

Originally posted by DaPi
Vote for pando's mom as moderator !

---

:D :D :D :D :D

Quote:

---

Originally posted by DaPi
Vote for pando's mom as moderator !

---

No, Super Moderator

Quote:

---

Originally posted by parijat67
hi all,

I could debug the entire pl/sql procedure and found that the
following delete statement is causing the oracle error: ora-03113.
---------------
delete FROM PROPERTIES_TEXT_LARGE a WHERE exists
(SELECT b.valueobjecttypeid, B.valueobjectid FROM PROPERTIES_OBJECTS b WHERE b.objecttypeid = 10217 AND b.valueobjecttypeid > 40000
and a.objecttypeid = b.valueobjecttypeid and a.objectid = b.valueobjectid);
---------------

Parijat Paul

---

I am just guessing if this is ur last part of code.
?

Hi,

no this delete statement is not the last one in the series of bulk deletes. if i remove this delete statement then rest of the deletes work fine. the explain plan shows that there are two full table scans for this delete statement. somehow there is no alert log generated against this error ora-03113. what i found in the trace files is a different error i.e "found dead shared server 'S000', pid = (8, 10)"
but cant say whether this has any relation to ora-03113 or something else has failed.

Paul

Just try random things until it works.

For example, the SELECT clause of an exists is generally thrown away, so the standard is to SELECT 1.

delete FROM PROPERTIES_TEXT_LARGE a WHERE exists
(SELECT 1 FROM PROPERTIES_OBJECTS b WHERE b.objecttypeid = 10217 AND b.valueobjecttypeid > 40000
and a.objecttypeid = b.valueobjecttypeid and a.objectid = b.valueobjectid);

Try adding a nested loop hint

Try using an IN instead of an EXISTS

Try using a hint with the IN

Try making the WHERE clause more or less restrictive.

Try anything you can to make it work. Then, once you have it working, you can take the one that doesn't work and the one that works and make incremental changes to each (making each more like the other) until the broken one works or the working one breaks to try to isolate the problem. Once you identify the true problem, you can decide how to work around it and whether or not to actually report it.

- Chris

hi,

thanks a lot for your mail. i tried all possible changes in the where clause but failed to stop the error. something is wrong in the delete statement. i removed the delete statement from the bulk deletes and tried to run it from my sql prompt. Still i found the same error. The rest of the deletes worked fine without any problem.

In the server side i got the following errors in the udump folder. This error is coming pretty often. The application server is throwing java.sql.sqlException:End of tns connection channel. we are runnig on pramati server and oracle 9.0.1.1.The driver class is using "oracle.jdbc.driver.OracleDriver".
=======================

Thu May 22 15:47:07 2003
found dead shared server 'S000', pid = (8, 5)
found dead shared server 'S002', pid = (15, 18)
Thu May 22 15:47:23 2003
found dead shared server 'S000', pid = (8, 6)
found dead shared server 'S001', pid = (15, 19)
found dead shared server 'S003', pid = (17, 3)
Thu May 22 15:48:15 2003
found dead shared server 'S000', pid = (8, 7)
Thu May 22 15:49:07 2003
found dead shared server 'S000', pid = (8, 8)
Thu May 22 15:49:35 2003
found dead shared server 'S000', pid = (8, 9)
Thu May 22 15:50:20 2003
found dead shared server 'S000', pid = (8, 10)
Thu May 22 15:50:22 2003


=======================
what is going wrong? when does this error occur? is it due to some wrong setup of oracle 9i?

waiting to har your replies...

thanks once again

Parijat Paul

Hmmm....

Since I'm too lazy to go look up the error (and since I don't have MetaLink anyway), I'll keep going at this blind.

What is different between this statement and the others?
Is this the only one that deletes from that table?
Can you do *any* deletes from that table?
Is that a partitioned table?
Are you running parellel server?
Is it a parallel statement?
What's the execution plan?

Again, I'd look at all these variables. I'd try a simple delete from the table by PK. Then by a range of PKs. Then by an exact set of objectid,objecttypeid values. Then a range. Then a sub-query by PK. etc. Find out what exactly is causing it to break. Watch the plan on every permutation.

Again, try *anything* to make it work. If delete by PK doesn't work, try a SELECT by PK. If not that, then SELECT * ... WHERE ROWNUM = 1. *Anything* that will work against that table. Then build it up to where it doesn't work.

- Chris