Re: Debug ORA-03113 on Oracle XE
From: CarlosAL <miotromailcarlos_at_netscape.net>
Date: Wed, 17 Jun 2009 07:06:36 -0700 (PDT)
Message-ID: <cf23098e-c0de-4b4e-820b-c8de918a30ef_at_r33g2000yqn.googlegroups.com>
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?
>
> --
> --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
> --
Date: Wed, 17 Jun 2009 07:06:36 -0700 (PDT)
Message-ID: <cf23098e-c0de-4b4e-820b-c8de918a30ef_at_r33g2000yqn.googlegroups.com>
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?
>
> --
> --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
> --
I'm not to fond to global temporary tables. Is there a sound reason for using them? They are stored in temporary segments, so you'd better check your configuration on this.
Cheers.
Carlos. Received on Wed Jun 17 2009 - 09:06:36 CDT