Re: Debug ORA-03113 on Oracle XE
- From: Shakespeare <whatsin@xxxxxxxxx>
- Date: Wed, 17 Jun 2009 21:23:24 +0200
Terry Dykstra schreef:
"joel garry" <joel-garry@xxxxxxxx> wrote in message news:fa016d85-ccfe-4ed1-9754-acb7e774e9d7@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jun 17, 7:57 am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH...@xxxxxxxxxxxxxx> wrote:CarlosAL escribió:
On 17 jun, 14:03, "Álvaro G. Vicario"The code was the result of refactoring a megahuge query that took 15
<alvaro.NOSPAMTH...@xxxxxxxxxxxxxx> wrote:
Álvaro G. Vicario escribió:I'm not to fond to global temporary tables. Is there a sound reasonI have a web application written in PHP that's failing on one computerI've been able to kind of isolate the problem. I'm running two queries
(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.
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?
for using them?
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 betterThank you, I'll look into it.
check your configuration on this.
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
--
@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
.
- References:
- Debug ORA-03113 on Oracle XE
- From: "Álvaro G. Vicario"
- Re: Debug ORA-03113 on Oracle XE
- From: "Álvaro G. Vicario"
- Re: Debug ORA-03113 on Oracle XE
- From: CarlosAL
- Re: Debug ORA-03113 on Oracle XE
- From: "Álvaro G. Vicario"
- Re: Debug ORA-03113 on Oracle XE
- From: joel garry
- Re: Debug ORA-03113 on Oracle XE
- From: Terry Dykstra
- Debug ORA-03113 on Oracle XE
- Prev by Date: Re: Debug ORA-03113 on Oracle XE
- Next by Date: Accesses News Group
- Previous by thread: Re: Debug ORA-03113 on Oracle XE
- Next by thread: Re: Debug ORA-03113 on Oracle XE
- Index(es):
Relevant Pages
|
