what is ora-03113 ??

[abhaysk]

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.
?

[parijat67]

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

[chrisrlong]

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

[parijat67]

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

[chrisrlong]

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