Re: Debug ORA-03113 on Oracle XE
Date: Wed, 17 Jun 2009 21:23:24 +0200
Terry Dykstra schreef:
> "joel garry" <joel-garry_at_home.com> wrote in message
> 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
> 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
> 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.
> _at_home.com is bogus.
> 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.
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