Re: Debug ORA-03113 on Oracle XE
From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com>
Date: Wed, 17 Jun 2009 16:57:06 +0200
Message-ID: <h1b085$lam$1_at_news.eternal-september.org>
CarlosAL escribió:
>> Á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?
Date: Wed, 17 Jun 2009 16:57:06 +0200
Message-ID: <h1b085$lam$1_at_news.eternal-september.org>
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.
> They are stored in temporary segments, so you'd better > check your configuration on this.
Thank you, I'll look into it.
-- -- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain -- Mi sitio sobre programación web: http://borrame.com -- Mi web de humor satinado: http://www.demogracia.com --Received on Wed Jun 17 2009 - 09:57:06 CDT