Problem connecting to Oracle "as sysdba" from Oracle SQL Developer
(Auckland, New Zealand)
I have an Oracle 10g XE database running on Windows Vista and I want to connect "as sysdba" from Oracle SQL Developer. When I test the connection I get the error ORA-01031: insufficient privileges.
There are a number of possible causes for this, so the best approach is to check them all.
Check the current Windows' user (the one you're logged on as) is a member of the ORA_DBA group.
To do this
- right click on My Computer
- select Manage
- then, Local Users and Groups
- then Groups
- then Oracle DBA Group (probably ora_dba)
- and ensure your username is in the list of members or add it, if not
Ensure sqlnet.ora in ORACLE_HOME\NETWORK\ADMIN\ contains the following line:
SQLNET.AUTHENTICATION_SERVICES = (NTS)
if not add it, then stop and restart the listener.
If you have the line
SQLNET.AUTHENTICATION_SERVICES = (none)
you must enter a password for SYS user which means you won't be able to connect "/ as sysdba".
Open tnsnames.ora (in $ORACLE_HOME\NETWORK\ADMIN\) and ensure the entry for your database(s) has a name for the host rather than an ip address.
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
In Oracle SQL Developer
open the dialogue box to create a new connection
- select the role as SYSDBA
- select "OS Authentication" to avoid having to supply a password
- change the connection type to "TNS"
- select "Connect Identifier"
- pick the relevant connection string
- finally, test the connection and everything should be fine
Having established how to connect to Oracle "as sydba" from SQL Developer, it is unlikely that you will need to do so very often. When you install Oracle XE, SQL*Plus is also installed and you can use this to start and stop the database.
You might also be interested in our Oracle tutorials
, our PL/SQL tutorials
(including how to wrote a "Hello World" program in 30 seconds) and our SQL tutorials
See our Oracle training
page for information about formal training courses both on-line and in person.