Debug ORA-03113 on Oracle XE
"Álvaro G. Vicario"
(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.
Can you suggest me some troubleshooting tips?
--
-- 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
--
Maxim Demenko
> 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.
>
> Can you suggest me some troubleshooting tips?
>
>
>
The first place where i would look up - alert.log, 3113 should be
recorded there as well as the preceding error ( which is very often an
ORA-00600 or ORA-07445, an additional trace file should be generated as
well ), when you figured out, from which error is the origin of 3113,
next step should be Metalink.
Best regards
Maxim
--
Why make things difficult, when it is possible to make them cryptic
and totally illogical, with just a little bit more effort?
Aksel Peter J�rgensen
"Álvaro G. Vicario"
> �lvaro G. Vicario schrieb:
>> 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.
> The first place where i would look up - alert.log, 3113 should be
> recorded there as well as the preceding error ( which is very often an
> ORA-00600 or ORA-07445, an additional trace file should be generated as
> well ),
I found "alert_xe.log". It doesn't mention 3113 but there're several
lines like:
Errors in file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_3928.trc:
ORA-07445: se ha encontrado una excepci�n: volcado de memoria
[ACCESS_VIOLATION] [_lxptmutf8+30] [PC:0x60AD606E] [ADDR:0xA98FD33]
[UNABLE_TO_READ] []
... and there's a 6 MB dump at "xe_ora_3928.trc". At least now I have
something to look at, thank you.
> when you figured out, from which error is the origin of 3113,
> next step should be Metalink.
I presume Metalink is only for pay customers, isn't it?
Maxim Demenko
> Maxim Demenko escribiďż˝:
>> �lvaro G. Vicario schrieb:
>>> 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.
>
>> The first place where i would look up - alert.log, 3113 should be
>> recorded there as well as the preceding error ( which is very often an
>> ORA-00600 or ORA-07445, an additional trace file should be generated
>> as well ),
>
> I found "alert_xe.log". It doesn't mention 3113 but there're several
> lines like:
>
> Errors in file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_3928.trc:
> [UNABLE_TO_READ] []
>
> ... and there's a 6 MB dump at "xe_ora_3928.trc". At least now I have
> something to look at, thank you.
>
>
>> when you figured out, from which error is the origin of 3113, next
>> step should be Metalink.
>
> I presume Metalink is only for pay customers, isn't it?
>
>
Sorry, have overlooked you are on XE.
In that case you can not get any support from Oracle ( you'll have to
upgrade your Oracle version - in that case your error may be not
reproducible at all). You can though try to ask on the OTN Oracle XE
forum, maybe somebody already experienced similar error and has a
workaround.
Best regards
Maxim
--
Why make things difficult, when it is possible to make them cryptic
and totally illogical, with just a little bit more effort?
Aksel Peter J�rgensen
CarlosAL
> Álvaro G. Vicario schrieb:
>
>
>
> > Maxim Demenko escribió:
> >> Álvaro G. Vicario schrieb:
> >>> 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.
>
> >> The first place where i would look up - alert.log, 3113 should be
> >> recorded there as well as the preceding error ( which is very often an
> >> ORA-00600 or ORA-07445, an additional trace file should be generated
> >> as well ),
>
> > I found "alert_xe.log". It doesn't mention 3113 but there're several
> > lines like:
>
> > Errors in file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_3928.trc:
> > [ACCESS_VIOLATION] [_lxptmutf8+30] [PC:0x60AD606E] [ADDR:0xA98FD33]
> > [UNABLE_TO_READ] []
>
> > ... and there's a 6 MB dump at "xe_ora_3928.trc". At least now I have
> > something to look at, thank you.
>
> >> when you figured out, from which error is the origin of 3113, next
> >> step should be Metalink.
>
> > I presume Metalink is only for pay customers, isn't it?
>
> Sorry, have overlooked you are on XE.
> In that case you can not get any support from Oracle ( you'll have to
> upgrade your Oracle version - in that case your error may be not
> reproducible at all). You can though try to ask on the OTN Oracle XE
> forum, maybe somebody already experienced similar error and has a
> workaround.
>
> Best regards
>
> Maxim
>
> --
> Why make things difficult, when it is possible to make them cryptic
> and totally illogical, with just a little bit more effort?
>
Álvaro:
Revisa tu 'shared pool'.
¿Tienes cursor_sharing=force?
HTH.
Saludos.
Carlos.
"Álvaro G. Vicario"
>>
>>
>>
>>> Maxim Demenko escribi�:
>>>> �lvaro G. Vicario schrieb:
>>>>> 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.
>>>> The first place where i would look up - alert.log, 3113 should be
>>>> recorded there as well as the preceding error ( which is very often an
>>>> ORA-00600 or ORA-07445, an additional trace file should be generated
>>>> as well ),
>>> I found "alert_xe.log". It doesn't mention 3113 but there're several
>>> lines like:
>>> Errors in file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_3928.trc:
>>> [ACCESS_VIOLATION] [_lxptmutf8+30] [PC:0x60AD606E] [ADDR:0xA98FD33]
>>> [UNABLE_TO_READ] []
>>> ... and there's a 6 MB dump at "xe_ora_3928.trc". At least now I have
>>> something to look at, thank you.
>>>> when you figured out, from which error is the origin of 3113, next
>>>> step should be Metalink.
>>> I presume Metalink is only for pay customers, isn't it?
>> Sorry, have overlooked you are on XE.
>> In that case you can not get any support from Oracle ( you'll have to
>> upgrade your Oracle version - in that case your error may be not
>> reproducible at all). You can though try to ask on the OTN Oracle XE
>> forum, maybe somebody already experienced similar error and has a
>> workaround.
> �lvaro:
>
> Revisa tu 'shared pool'.
>
> �Tienes cursor_sharing=force?
Tengo lo que venga de f�brica con el XE. En Google me salen toneladas de
documentaci�n. �Qu� miro?
"Álvaro G. Vicario"
I've edited this file:
C:\oraclexe\app\oracle\product\10.2.0\server\config\scripts\init.ora
After checking they didn't already exist, I added these directives:
cursor_sharing=force
shared_pool_size=52428800
Then I restarted the server. Now I can no longer log in into the
database so I assume I did something wrong...
Shakespeare
Shakespeare
joel garry
<alvaro.NOSPAMTH...@demogracia.com> wrote:
> Álvaro G. Vicario escribió:
>
>
>
> > CarlosAL escribió:> >>> Álvaro G. Vicario schrieb:
>
> >>>> Maxim Demenko escribió:
> >>>>> Álvaro G. Vicario schrieb:
> >>>>>> 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.
> >>>>> The first place where i would look up - alert.log, 3113 should be
> >>>>> recorded there as well as the preceding error ( which is very often an
> >>>>> ORA-00600 or ORA-07445, an additional trace file should be generated
> >>>>> as well ),
> >>>> I found "alert_xe.log". It doesn't mention 3113 but there're several
> >>>> lines like:
> >>>> Errors in file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_3928.trc:
> >>>> [ACCESS_VIOLATION] [_lxptmutf8+30] [PC:0x60AD606E] [ADDR:0xA98FD33]
> >>>> [UNABLE_TO_READ] []
> >>>> ... and there's a 6 MB dump at "xe_ora_3928.trc". At least now I have
> >>>> something to look at, thank you.
> >>>>> when you figured out, from which error is the origin of 3113, next
> >>>>> step should be Metalink.
> >>>> I presume Metalink is only for pay customers, isn't it?
> >>> Sorry, have overlooked you are on XE.
> >>> In that case you can not get any support from Oracle ( you'll have to
> >>> upgrade your Oracle version - in that case your error may be not
> >>> reproducible at all). You can though try to ask on the OTN Oracle XE
> >>> forum, maybe somebody already experienced similar error and has a
> >>> workaround.
>
>
> >> Revisa tu 'shared pool'.
>
> >> ¿Tienes cursor_sharing=force?
>
> > documentación. ¿Qué miro?
>
> I've edited this file:
>
> C:\oraclexe\app\oracle\product\10.2.0\server\config\scripts\init.ora
Maybe I've been confused because this is XE, but I don't think that is
the correct file. There should be an spfile named C:\oraclexe\app
\oracle\product\10.2.0\server\dbs\SPFILEXE.ORA that your db is using,
which is not editable. You either have to use alter commands to
change stuff, or create a pfile from spfile and edit that, then
specify it during the startup, then create spfile from pfile to make
it persistent if it works.
>
> After checking they didn't already exist, I added these directives:
>
> cursor_sharing=force
> shared_pool_size=52428800
>
> Then I restarted the server. Now I can no longer log in into the
> database so I assume I did something wrong...
I suspect you have memory problems. What else is running on the
laptop, and how much physical memory does it have? Since sqlplus was
working, I'm guessing something besides Oracle was sucking up your
memory. There are some mysterious bugs fixed in later versions that
may also be involved, perhaps keeping it in sqlplus with no actual
results returned just barely doesn't blow memory.
Also, if you get it running again, from the sqlplus as / command line:
show parameter pool
You may need to export your user data and reinstall XE.
jg
--
@home.com is bogus.
“Mommy had her 'mad face' on.” - 8 year old testifying about being
shot by an off-duty police officer when his drug-addled mother tried
to run the officer down during a road-rage incident.
CarlosAL
I seem to remember something related to cursor_sharing=force to cause
ORA-07445. I was only asking, not suggesting.
I agree with Joel. Check your memory usage and Oracle parameters
related to it (in spfile).
HTH.
Cheers.
Carlos.
"Álvaro G. Vicario"
>>>>> Revisa tu 'shared pool'.
>>>> Tengo lo que venga de f�brica con el XE. En Google me salen toneladas de
>>>> documentaci�n. �Qu� miro?
>>> I've edited this file:
>>> C:\oraclexe\app\oracle\product\10.2.0\server\config\scripts\init.ora
>> Maybe I've been confused because this is XE, but I don't think that is
>> the correct file. There should be an spfile named C:\oraclexe\app
>> \oracle\product\10.2.0\server\dbs\SPFILEXE.ORA that your db is using,
>> which is not editable. You either have to use alter commands to
>> change stuff, or create a pfile from spfile and edit that, then
>> specify it during the startup, then create spfile from pfile to make
>> it persistent if it works.
>>
>>
>>
>>> After checking they didn't already exist, I added these directives:
>>> cursor_sharing=force
>>> shared_pool_size=52428800
>>> Then I restarted the server. Now I can no longer log in into the
>>> database so I assume I did something wrong...
>> I suspect you have memory problems. What else is running on the
>> laptop, and how much physical memory does it have? Since sqlplus was
>> working, I'm guessing something besides Oracle was sucking up your
>> memory. There are some mysterious bugs fixed in later versions that
>> may also be involved, perhaps keeping it in sqlplus with no actual
>> results returned just barely doesn't blow memory.
>>
>> Also, if you get it running again, from the sqlplus as / command line:
>>
>> show parameter pool
>>
>> You may need to export your user data and reinstall XE.
> I seem to remember something related to cursor_sharing=force to cause
> ORA-07445. I was only asking, not suggesting.
Anyway, I took your suggestion as a starting point and I learnt about
ALTER SYSTEM. This is the default config in XE:
SQL> show parameter pool;
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 0
large_pool_size big integer 0
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 5033164
shared_pool_size big integer 0
streams_pool_size big integer 0
I've specified several sizes where I was allowed but I didn't notice any
difference.
> I agree with Joel. Check your memory usage and Oracle parameters
> related to it (in spfile).
I'm into it.
I've been finally able to reproduce the issue in a different box.
Apparently, it only happens with specific data, but it's pretty easy to
get data that fails. It seems to be specific to Oracle 10g XE (I cannot
make it fail with a regular 9 server).
If I can't fix it, I guess my best option right now will be to rewrite
the module in the PHP application so it uses different queries that
hopefully won't trigger an access violation.
--
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci�n web: http://borrame.com
"Álvaro G. Vicario"
>>>>>>>> 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.
>>>>>>> The first place where i would look up - alert.log, 3113 should be
>>>>>>> recorded there as well as the preceding error ( which is very often an
>>>>>>> ORA-00600 or ORA-07445, an additional trace file should be generated
>>>>>>> as well ),
>>>>>> I found "alert_xe.log". It doesn't mention 3113 but there're several
>>>>>> lines like:
>>>>>> Errors in file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_3928.trc:
>>>>>> [ACCESS_VIOLATION] [_lxptmutf8+30] [PC:0x60AD606E] [ADDR:0xA98FD33]
>>>>>> [UNABLE_TO_READ] []
>>>>>> ... and there's a 6 MB dump at "xe_ora_3928.trc". At least now I have
>>>>>> something to look at, thank you.
>>>>>>> when you figured out, from which error is the origin of 3113, next
>>>>>>> step should be Metalink.
>>>>>> I presume Metalink is only for pay customers, isn't it?
>>>>> Sorry, have overlooked you are on XE.
>>>>> In that case you can not get any support from Oracle ( you'll have to
>>>>> upgrade your Oracle version - in that case your error may be not
>>>>> reproducible at all). You can though try to ask on the OTN Oracle XE
>>>>> forum, maybe somebody already experienced similar error and has a
>>>>> workaround.
>>>> Revisa tu 'shared pool'.
>>> Tengo lo que venga de f�brica con el XE. En Google me salen toneladas de
>>> documentaci�n. �Qu� miro?
>> I've edited this file:
>>
>> C:\oraclexe\app\oracle\product\10.2.0\server\config\scripts\init.ora
>
> Maybe I've been confused because this is XE, but I don't think that is
> the correct file. There should be an spfile named C:\oraclexe\app
> \oracle\product\10.2.0\server\dbs\SPFILEXE.ORA that your db is using,
> which is not editable. You either have to use alter commands to
> change stuff, or create a pfile from spfile and edit that, then
> specify it during the startup, then create spfile from pfile to make
> it persistent if it works.
There it is:
xe.__java_pool_size=4194304
xe.__large_pool_size=8388608
xe.__shared_pool_size=104857600
xe.__streams_pool_size=8388608
*.audit_file_dest='C:\oraclexe\app\oracle\admin\XE\adump'
*.background_dump_dest='C:\oraclexe\app\oracle\admin\XE\bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oraclexe\oradata\XE\control.dbf'
*.core_dump_dest='C:\oraclexe\app\oracle\admin\XE\cdump'
*.db_name='XE'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.DB_RECOVERY_FILE_DEST='C:\oraclexe\app\oracle\flash_recovery_area'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
*.job_queue_processes=4
*.open_cursors=300
*.os_authent_prefix=''
*.pga_aggregate_target=80M
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.sga_target=240M
*.shared_servers=4
*.undo_management='AUTO'
*.undo_tablespace='UNDO'
*.user_dump_dest='C:\oraclexe\app\oracle\admin\XE\udump'
I've played around with ALTER SYSTEM (actually, it's a faster way to
test stuff) but changing the *_pool_size parameters didn't have any
effect on the issue.
> I suspect you have memory problems. What else is running on the
> laptop, and how much physical memory does it have? Since sqlplus was
> working, I'm guessing something besides Oracle was sucking up your
> memory.
The laptop has 3GB of RAM and most of it is free. However, the XE
edition claims to allow a limited amount of memory (1GB) so it's easy
that I'm hitting a memory limit somewhere.
> There are some mysterious bugs fixed in later versions that
> may also be involved, perhaps keeping it in sqlplus with no actual
> results returned just barely doesn't blow memory.
Well, the free edition is seldom updated so I'll have to live with 10g
R2 for a while.
> Also, if you get it running again, from the sqlplus as / command line:
>
> show parameter pool
SQL> show parameter pool;
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 0
large_pool_size big integer 0
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 5033164
shared_pool_size big integer 0
streams_pool_size big integer 0
> You may need to export your user data and reinstall XE.
Data is disposable (I regenerate it all from SQL scripts) and I've
reinstalled XE two or three times but the problem is not specific to the
laptop: I've learnt that it happens on any 10g XE installation when I
happen to generate certain data from the application, but it won't fail
in Oracle 9.
The "alert_xe.log" file shows this:
Errors in file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_5500.trc:
ORA-07445: se ha encontrado una excepci�n: volcado de memoria
[ACCESS_VIOLATION] [_lxptmutf8+30] [PC:0x60AD606E] [ADDR:0xB40DEDF]
[UNABLE_TO_READ] []
Having an access violation makes me think that I've just hit a bug in
the Oracle engine. Unless I'm able to find an obvious misconfiguration
issue, I guess it'll be easier to just rewrite my query.
Thanks a lot for your tips.
--
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci�n web: http://borrame.com
"Álvaro G. Vicario"
> 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?
CarlosAL
> -- Mi sitio sobre programación web:http://borrame.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.
"Álvaro G. Vicario"
> On 17 jun, 14:03, "�lvaro G. Vicario"
> <alvaro.NOSPAMTH...@demogracia.com> wrote:
>> �lvaro G. Vicario escribi�:
> 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
joel garry
<alvaro.NOSPAMTH...@demogracia.com> wrote:
> CarlosAL escribió:
>
>
>
> > On 17 jun, 14:03, "Álvaro G. Vicario"
> > <alvaro.NOSPAMTH...@demogracia.com> wrote:
> >> Álvaro G. Vicario escribió:
>
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
--
@home.com is bogus.
Terry Dykstra
news:fa016d85-ccfe-4ed1...@j18g2000yql.googlegroups.com...
On Jun 17, 7:57 am, "�lvaro G. Vicario"
<alvaro.NOSPAMTH...@demogracia.com> wrote:
> CarlosAL escribi�:
>
>
>
> > On 17 jun, 14:03, "�lvaro G. Vicario"
> > <alvaro.NOSPAMTH...@demogracia.com> wrote:
> >> �lvaro G. Vicario escribi�:
>
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.
--
Terry Dykstra
Shakespeare
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
"Álvaro G. Vicario"
> 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.
For the records: it became clear that I was possibly hitting a resource
limit of the Express Edition so I finally split my query in several
pieces (with the help of one more temporary table) and now its working
again.
Thank you all for your tips and hints. I've learnt a lot with this issue :)