Re: Debug ORA-03113 on Oracle XE

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 17 Jun 2009 21:23:24 +0200
Message-ID: <4a3942ae$0$186$e4fe514c_at_news.xs4all.nl>

Terry Dykstra schreef:
> "joel garry" <joel-garry_at_home.com> wrote in message
> news:fa016d85-ccfe-4ed1-9754-acb7e774e9d7_at_j18g2000yql.googlegroups.com...
> On Jun 17, 7:57 am, "Álvaro G. Vicario"
> <alvaro.NOSPAMTH..._at_demogracia.com> wrote:

>> CarlosAL escribió:
>>
>>
>>
>>> On 17 jun, 14:03, "Álvaro G. Vicario"
>>> <alvaro.NOSPAMTH..._at_demogracia.com> wrote:
>>>> Álvaro G. Vicario escribió:
>>>>> I have a web application written in PHP that's failing on one computer
>>>>> (and only that computer): a laptop that runs the Oracle 10g Express
>>>>> Edition. It fails when it executes a specific SELECT query: I get a
>>>>> "ORA-03113 end-of-file on communication channel" error and the
>>>>> connection drops.
>>>>> My main concern is that I can't figure out what to do next. It seems
>>>>> that ORA-03113 is a very generic error that can happen for lots of 
>>>>> reasons.
>>>>> The query returns no rows with the current data set but it's pretty
>>>>> complex (lots of joins and subqueries). However, I only get the
>>>>> ORA-03113 error when I run it through PHP and Apache. When I run it 
>>>>> from
>>>>> SQL Developer I get no error and the results come almost instantly. 
>>>>> The
>>>>> PHP backtrace doesn't provide additional info. The PHP and Apache
>>>>> versions do not seem to matter. There're neither antivirus nor 
>>>>> firewall
>>>>> in the laptop. If Oracle XE generates further info I don't know where 
>>>>> I
>>>>> can find it.
>>>> I've been able to kind of isolate the problem. I'm running two queries
>>>> and both involve a temporary table. This is the flow:
>>>> 1. INSERT data in a temporary table
>>>> 2. Run a SELECT joining several tables (including the temp table)
>>>> It's a transaction temporary table and its data gets discarded because
>>>> it's no longer needed.
>>>> - Using a session (rather than transaction) temporary table makes no
>>>> difference.
>>>> - If I ROLLBACK before the query there's no ORA-03113 (but of course 
>>>> the
>>>> temporary data is gone).
>>>> - If I use a regular table there's no ORA-03113 (though I'll need to
>>>> redesign the module to separate the data from the different sessions 
>>>> and
>>>> remove it when no longer need it).
>>>> The redesign is always an option but... What server settings do you
>>>> think that could be causing the ORA-03113 when using temporary tables?
>>> I'm not to fond to global temporary tables. Is there a sound reason
>>> for using them?
>> The code was the result of refactoring a megahuge query that took 15
>> minutes to run by dividing it in two big queries that took 5 seconds
>> each. As about temporary vs regular, I just read Oracle had such feature
>> and I figured out it could be handy: automagical session isolation and
>> data removal looked cool at the time.

>
> Yes, it is a cool feature.
>

>>> They are stored in temporary segments, so you'd better
>>> check your configuration on this.
>> Thank you, I'll look into it.
>>

>
> Since it is replicable on different installations, and is dependent on
> specific SQL as well as temporary segments, it is likely one of
> several things (if indeed it is Oracle):
>
> Temporary segment configuration, as Carlos suggested. This could have
> something to do with the limitations of XE as well, perhaps you could
> download SE and EE for testing purposes, and see if they have the same
> problem. Somewhere I got the idea XE is a full-featured Oracle with
> some memory and disk limitations, which could mean they simply didn't
> get that quite right for temporary segments. See the interesting
> thread
> http://forums.oracle.com/forums/thread.jspa?threadID=910059&start=0&tstart=0
> for some ideas of what to play with - perhaps turning off pga
> management and using sort_area instead, to force things out of memory
> onto disk with these complex SQL could slow it down but make it work.
>
> Wrong results, optimizer or temp seg bug. If you can, see metalink
> Note: 401436.1 and you can drill down to look at various interesting
> bugs. If SE or XE has the same problem, you could patch to 10.2.0.4
> and see if it is fixed. It is worth it to get metalink access, even
> if you are just using XE.
>
> Of course, since you now have a workaround, you may not want to
> bother.
>
> Normally, to get support, you would have to come up with a replicable
> test case including ddl and data on the latest supported patch set.
> It may be worthwhile doing that and letting the XE people on OTN know
> about it, so they can try it on the 11 version.
>
> jg
> --
> _at_home.com is bogus.
> http://www3.signonsandiego.com/stories/2009/jun/17/1b17myspace212851-myspace-lay-nearly-30-percent-st/?uniontrib
>
>
> XE is most definitely not EE with memory/disk limitations. It is actually
> missing functionality. One that I ran across was the absence of the
> utl_file package.
>

 From
http://feuerthoughts.blogspot.com/2006/03/some-gotchas-with-oracle-xe.html

(Steven Feuerstein):

UTL_FILE is not available. Usually, when you install Oracle, the UTL_FILE package (used to read/write files within PL/SQL) is installed, EXECUTE is granted to PUBLIC, and a public synonym is created. With XE, the package is installed and the synonym created, but the GRANT EXECUTE has not been run.

To fix this problem, connect to a SYSDBA account and run the $ORACLE_HOME/RDBMS/Admin/utlfile.sql file, or simply execute this command (from a SYSDBA account):

GRANT EXECUTE on SYS.UTL_FILE TO PUBLIC
/

HTH Shakespeare Received on Wed Jun 17 2009 - 14:23:24 CDT