Problem connecting to Oracle "as sysdba" from Oracle SQL Developer

by John
(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.


SQL Developer - new connection

---------------------------


There are a number of possible causes for this, so the best approach is to check them all.

Step 1

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

Step 2

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".

Step 3

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.

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

Step 4

In Oracle SQL Developeropen 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

  • SQL Developer - new connection

    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.

    Comments for Problem connecting to Oracle "as sysdba" from Oracle SQL Developer

    Average Rating starstarstarstarstar

    Click here to add your own comments

    Jul 29, 2015
    True information is shared NEW
    by: Virginia

    Many student have syntax error in their writing. They can use many method of solving these error but they can’t improve their writing level. They should to read our buy essay online review where they meet our expert and experienced writers and they can solve all these problem.

    Jul 14, 2015
    doubt
    by: Anonymous

    thanks for your detailed explanation , when I am doing as you said I am getting an error that
    "test failed- no ocijjdbc12 in java.library.path".

    Kindly suggest the resolution as soon as possible I am in a terrible need of that.

    May 22, 2015
    Great article
    by: Rhonda

    Great article

    Apr 09, 2015
    Good to read
    by: Kimberly G. Couch

    I will be content to help save this web site in to our folder. Many thanks! The way you express yourself is awesome.Hey, your blog is great.

    Apr 19, 2014
    Good
    by: Lenin

    Thank you for the post, really good step by step analysis..

    Jan 19, 2013
    Thanks
    by: Jyoti

    Thanks a Lot
    it worked

    Dec 14, 2011
    It works. Thanks
    by: Muthu

    It works. Thanks.

    Click here to add your own comments

    Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.