ORA-01031: insufficient privileges

How many times have you come across ORA-01031 error in Windows ?, the error message in itself makes sense only in Windows because the same in UNIX could be resolved with a simple "chown -R" command.

ORA-01031: insufficient privileges

The error puzzles an individual so much that he/she considers changing the user privileges on the very first go, which is one of the steps to resolve this error though. But I had to do something else to resolve it to get into the SQL prompt.

I had to install an Oracle server on my PC and it did get successful with a starter database. I created some schemas, tables and other objects of my own choice then shut the DB down and shut my computer as well when done completely. The next day when I wanted to login to the DB for some more R & D, I get the so called " ORA-01031: insufficient privileges " error.

I was struck dumb for a while as no one else logged into my PC since it went down and I had logged into the system with the usual login id that I use. But then I remembered changing something in the sqlnet.ora file the previous day, I thought of rolling back the changes to make it work and fortunately it worked.

I have always had a habit of commenting "NAMES.DEFAULT_DOMAIN" parameter as I did not want it to bother the connections coming in/going out between different databases on my PC, but along with it I commented something else which was "SQLNET.AUTHENTICATION_SERVICES". The parameter had a value of "NTS" within parentheses. When "SQLNET.AUTHENTICATION_SERVICES" was uncommented and tried again, I was able to login back to the database. The contents of my sqlnet.ora file are as shown below.


# SQLNET.ORA Network Configuration File: E:\oracle\ora91_EE\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

#NAMES.DEFAULT_DOMAIN = home.com

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)


Upon doing some more R & D with different PCs having multiple Oracle installations, it was found that following error is also reported ORA-12705: invalid or unknown NLS parameter value specified when the ORACLE_HOME is not set appropriately for a particular ORACLE_SID. Before we proceed any further, we need to understand the reference of ORACLE_HOME taken from two locations.

Location-1
Start -> Programs -> Oracle Installation Products -> Home Selector: This shows a drop down box with the names of Oracle installations and their default ORACLE_HOME path. This has to be an appropriate value for the version of DB you want to work with.

Location-2
Right Click My Computer -> Properties -> Advanced(tab) -> Environmental Variables: There is a section called System Variables which shows up all the system wide environmental variables that are set already, ORACLE_HOME is one among them. Unless ORACLE_HOME is set in here,echo %ORACLE_HOME% from the command prompt does not show the value of ORACLE_HOME. It is observed that this takes precedence over the above mentioned Location 1.

The ORA-12705 error came out of the blue when the ORACLE_HOME for a particular ORACLE_SID was not set correctly but got resolved immediately with the precise value of ORACLE_HOME set in System Variables(Location 2).

There are so many other workarounds linked to resolving ORA-01031 error which an individual applies only when confronted with such situations or by simply poking around at MetaLink.