what is ora-03113 ??

[parijat67]

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

[nametullah]

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

[ganga]

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

[Sureshy]

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.

[chrisrlong]

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

[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);
---------------

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

[pando]

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

[DaPi]

Vote for pando's mom as moderator !

[pando]

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

[adewri]

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

No, Super Moderator