Category Archives: Oracle

How to enable Operating System authentication on Oracle XE

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

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.