Tag Archives: Oracle

Simple Solution to ORA-12520 on Oracle XE

11 Replies

If you get the following error on Oracle XE:

ORA-12520: TNS:listener could not find available handler for requested type of server

This is probably because the value for the processes parameter is too low.

For some reason, Oracle set the default value of the processes parameter on Oracle XE  to 40. If your application opens many process or  several queries are executed at the same time, the ORA-12520 could be displayed.

The solution for this issue is simple.  All you need to do is give the processes parameter an higher value.

open sql plus (All Programs -> Oracle Database 10g Express Edition -> Run SQL Command Line) and run the following command:
Alter system set processes=100 scope=spfile;

Restart the DB:
shutdown immediate
Startup

if needed, an higher number can be provided.

Possible solutions for ORA-00600

Leave a reply

If you get the following error:

ORA-00600:Internal error code, arguments: [15610],[],[],[],[],[]
This could be due to Oracle bug 4648181.
This error is usually manifested on Oracle 10g R1 systems (10.1.0.4 and 10.1.0.5) and on earlier version of Oracle 10g R2 (10.2.0.1 and 10.2.0.2) when performing a join.
According to Oracle this is an  Optimizer related bug and it is fixed in Oracle 10.2.0.3 and in 11.1.0.6. patch set  so the best solution would be to upgrade to these version.

This issue was also slated to be fixed in a 10.1.0.6 patch set that was expected mid 2007 but it looks like this fix is delayed. However,  if you have a justified business reason, it is possible to ask Oracle for interim fix.

If any of the options above is not suitable to your needs there are several workaround that you can try:
1) Since this issue is related to the cost based optimizer a simple solution can be to change the query. If you change even a simple thing like the order of the tables in the FROM clause of the query it will force the optimizer to analyze the query differently and may avoid the problem.
of course, this solution can make it perform slower and not always work but it could be a quick and dirty short run solution.
2) Turn off the optimizer cost base transformation by modifying init.ora

set “_optimizer_cost_based_transformation”=off;

This change can be done in the session level as well:

alter session set “_optimizer_cost_based_transformation”=off;

Note: it is usually not recommended to change Oracle underscore _ “hidden settings” but Oracle published this solution in Metalink so I assume it should be OK.

3) Force oracle to use earlier versions optimizer features by modifying init.ora
OPTIMIZER_FEATURES_ENABLE = 10.1.0

This setting will force Oracle to use the optimizer that was used in version 10.1.0 or any other version you provide (rule based).  Any optimizer features that were added after this version will be disabled.

How to enable Operating System authentication on Oracle XE

Leave a reply

OS authentication is not enabled by default in Oracle XE. To allow the use of OPS$ users, open sql plus (All Programs -> Oracle Database 10g Express Edition -> Run SQL Command Line) and run the following command:
alter system set os_authent_prefix=OPS$ scope=spfile;

Restart the DB:
shutdown immediate
Startup

To Create the OS user:

CREATE USER “OPS$DOMAIN\DOMAINUSER” PROFILE

“DEFAULT” IDENTIFIED EXTERNALLY DEFAULT

TABLESPACE “TABLESPACENAME”

TEMPORARY TABLESPACE “TEMPTABLESPACENAME” ACCOUNT

UNLOCK;

You can grant  the appropriate roles just like any other user

GRANT CONNECT, RESOURCE  TO

“OPS$ DOMAIN\DOMAINUSER “;

A quick and dirty fix for ORA-01008

Leave a reply

If a “ORA-01008 Not All Variables Bound” error is displayed, this could probably be related to oracle bug 5254759.
This could be solved by upgrading to 10.2.04 or installing the interim patch.
However if you need a quick and diry workaround you can try to eneable closing cursor on the dsn:

1) Open the ODBC administrator (control panel -> administrative tools -> ODBC)
2) Choose your DSN and click “Configure”
3) In the application tab, check the “enable closing cursor” checkbox.

It is important to note that this solution may not always work and it will probably result in a notable performance reduction but it may help until the patchset is applied.