Re: Debug ORA-03113 on Oracle XE
Date: Wed, 17 Jun 2009 11:39:28 +0200
Message-ID: <h1adkg$ahu$1_at_news.eternal-september.org>
CarlosAL escribió:
>>>>> 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?
>>> 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 -- Mi web de humor satinado: http://www.demogracia.com --Received on Wed Jun 17 2009 - 04:39:28 CDT