Re: ORA-03113 Error

"Al Willis" <alwillisj01_at_sbcglobal.net> wrote in message news:<3fff28a4$1_8_at_news.athenanews.com>...
> Paul,
>
> Thanks for your patient answer.
>
> I'm only using the version of Oracle downloaded from their website. I'm
> just a poor developer trying to get Oracle to work with my application.
>
> When I try the query from SQL*Plus I get the following errors:
>
> SP2-0642: SQL*Plus internal error state 2133, context 0:0:0
> Unsafe to proceed
> SP2-0642: SQL*Plus internal error state 2221, context 4294967295:4:0
> Unsafe to proceed
>
> I tried using the following and it worked:
>
> SELECT A.* FROM IM.LOOPDAT A
> WHERE LOOPNO IN (
> SELECT DISTINCT XA.LOOPNO FROM IM.LOOPDAT XA
> LEFT OUTER JOIN IM.LOOPCON XB ON XB.LOOPNO = XA.LOOPNO
> WHERE (XA.LOOPNO LIKE '24%') )
>
> Unfortunately this doesn't work when I have more than one search field.
>
> As you've hinted I may have a problem with the number of fields in the
> table. I believe that I have a valid reason for the table structure and it
> works OK under Interbase, and SQL Server. The end-users really don't have
> to manually input into the fields. My application populates most, (but not
> all) of the fields. However for searches and updates all of the fields have
> to be available whether they are in one table or spread among several.
>
> I had reduced the problem down to the minimum for problem reproduction. My
> application is really joining 8 tables to LOOPDAT, not just the 1 that I
> showed. I just hated to have to split up LOOPDAT because of the additional
> work that it entails. My application is being written to handle 6 different
> database backends, so I may have to bite the bullet one more time and make a
> change to accommodate a database that responds differently to some
> particular input or setup. From reading the newsgroup postings on
> ORA-03113, it seems to be somewhat difficult to solve, so I going to
> surrender now and take a different path.
>
> Many thanks for your help.
>
> Al

Al,

the # of cols > 255 is just a wild !@# guess. It is not surpring that something in the 9.2.0.1.0 release (base) would cause you grief.

We didn't use 9.2 in production until 9.2.0.4 as utilities such as export didn't work properly until that patchset.

Since you provided the table create scripts, I tried to reproduce the error in a database patched to 9.2.0.4.0 (w2k svr sp4). Connected with sqlplus, with no rows of data in either table, I could not reproduce the error.

I saw no notes in metalink regarding this, but I did see a posting with this error message that indicated a similar case having bug #1364334 which had been fixed in 9i.

What is your workarea policy?
If it is automatic, what is your pga_aggregate_target set to? It it is manual, what is your sort_area_size?

try hiking the sort_area_size and see if it completes successfully. I used a sort_area_size of 8M, but 1M is a good starting point.

hth.

Pd Received on Sat Jan 10 2004 - 10:11:10 CST