FB

How to Resolve Oracle Connection Issues

31 Comments / Last updated: January 30, 2022

One of the hardest parts about learning Oracle SQL is getting the database connection working.

After you download Oracle (either Oracle Express or the full database), and an IDE of your choice (such as SQL Developer), it should be easy to set up a new connection and log in.

However, it’s not.

By far, the most common question I receive in my Introduction to Oracle SQL course is how to connect to the database. There were quite a few steps involved in 11g, but now that Oracle Express has released 18c, there are a few more issues that appear.

This article explains what Oracle connection issues may occur and how to fix them.

 

Errors You Might Get When Connecting to Oracle

Here are some of the errors you might get when trying to connect to an Oracle 12c or 18c database.

 

Invalid Common User or Role Name

ORA-65096: invalid common user or role name
65096. 00000 -  "invalid common user or role name"
*Cause:    An attempt was made to create a common user or role with a name
that was not valid for common users or roles.  In addition to
the usual rules for user and role names, common user and role
names must start with C## or c## and consist only of ASCII
characters.
*Action:   Specify a valid common user or role name.

 

IO Error

Status : Failure - Test failed: IO Error : 
The network Adaptor could not establish the connection.

 

User Does Not Exist

ORA-01917: user or role 'INTRO_USER' does not exist
1917. 00000 -  "user or role '%s' does not exist"
*Cause:    There is not a user or role by that name.
*Action:   Re-specify the name.

 

SID Unknown

ORA-12505 error. TNS: listener doesn't know of SID given in connect descriptor.

 

Invalid Username/Password

ORA-01017: invalid username/password; logon denied

 

As you can see, there are quite a few different errors you can get when attempting to connect to an Oracle database.

 

Solutions to Oracle Database Connection Issues

Here are the solutions to the connection issues. I’ve listed them briefly here, and more details are available below.

  1. Ensure the Oracle Database is running
  2. Use the correct SID or Service Name for Pluggable Databases
  3. Login to the system accounts correctly
  4. Passwords are case sensitive

 

Ensure the Oracle Database is running

The first thing to do is to check that the Oracle database is running.

It should be running by default, but it’s an easy thing to check and nothing will work if the database isn’t running.

Sometimes I’ve noticed the database isn’t running if I restart my computer or have some other issue.

To start the Oracle database:

  1. Go to Start > Oracle
  2. Click on Start Oracle Database
  3. A command window is opened which runs Oracle database. Once it closes, the database should be running

You can check it is running by right-clicking the Start menu and selecting Services.

Scroll down to Oracle and check that the Oracle Database entry is set to Running.

Ensuring Oracle is running will sometimes resolve the following errors:

  • Status : Failure – Test failed: IO Error : The network Adaptor could not establish the connection.
  • ORA-12505 error. TNS: listener doesn’t know of SID given in connect descriptor.

If you still get these errors, there are more steps you can try.

 

Use the correct SID or Service Name for Pluggable Databases

In Oracle 11g and prior versions, when you installed Oracle/Oracle Express, you got one database.

However, as of 12c (so in versions 12c, 18c, and 19c), a concept was introduced called “container and pluggable databases”.

It allows for a set of rules and standards to be applied to a “container database”, which is kind of like a template or a parent database.

The “pluggable database” is where the real action happens. This is where data is stored and users are meant to be created. It inherits some information from the container database.

It’s kind of like the inheritance concept in object-oriented programming (if you’re familiar with that).

You can read more about this here: A Guide to PDBs and CDBs.

Why do you need to know about this?

Because, if you attempt to create a new user on the container database, you’ll get an error that doesn’t make any sense:

ORA-65096: invalid common user or role name
65096. 00000 -  "invalid common user or role name"
*Cause:    An attempt was made to create a common user or role with a name
that was not valid for common users or roles.  In addition to
the usual rules for user and role names, common user and role
names must start with C## or c## and consist only of ASCII
characters.
*Action:   Specify a valid common user or role name.

So why are you getting this “ORA-65096: invalid common user or role name” error?

Because you are attempting to create a new user on the container database. The solution is to create this new user on the pluggable database.

How do you create a new user on the pluggable database?

You need to log in to the pluggable database and create the user from there.

So, if you get this error:

  • Create a new connection to the pluggable database using an existing user (e.g. SYS)
  • Connect using this new connection
  • Run the Create User command
  • Create a new connection with the new user.

 

Step 1: Create a new connection to the pluggable database

In SQL Developer, create a new connection. In the new connection window, enter the following details:

  • Name: any name you want to use for your connection.
  • Username: SYS (or a user that has administrative rights)
  • Role: SYSDBA (if the username entered is SYS)
  • Password: the password for this account
  • Host: the location of your database, such as “localhost”
  • Port: 1521 (the default)

New Oracle Connection as SYS

Change the radio button from SID to Service Name, and enter a Service Name of XEPDB1.

This assumes you’re running Oracle Express. If not, your Service Name will be something different.

Entering this Service Name will let you connect to the pluggable database.

Test your connection, and if it is successful, save the connection.

 

Step 2: Connect

Now your new connection is created, it’s time to connect to it.

Right-click on the connection in the Connection panel on the left and select Connect.

Right click and connect to Oracle

You’ll then be connected to the database, and a new SQL window will open, allowing you to run commands.

 

Step 3: Run the Create User command

Now that you are connected to the database as an administrator (e.g. SYS), you can now create a new user.

For a new user to connect to a database, it needs to have a few privileges:

  • Connect
  • Create Session
  • Tablespace

I also like to give it Create Table privileges, so it can actually create tables.

Here’s the code to create a new user:

CREATE USER intro_user IDENTIFIED BY mypassword;
GRANT CONNECT TO intro_user;
GRANT CREATE SESSION, GRANT ANY PRIVILEGE TO intro_user;
GRANT UNLIMITED TABLESPACE TO intro_user;
GRANT CREATE TABLE TO intro_user;

Replace the username “intro_user” with whatever you want your username to be, and the “mypassword” with whatever password you want.

Run this code, and the new user is created.

 

Step 4: Connect as the New User

Finally, with the new user created, it’s time to create a new connection with this new user. This is the connection you can use each time you access the Oracle database.

It’s a similar process to step 1. Create a new connection in SQL Developer:

  • Name: any name you want to use for your connection.
  • Username: the username you added in Step 3
  • Password: the password for this account that you specified in step 3
  • Host: the location of your database, such as “localhost”
  • Port: 1521 (the default)
  • Service Name: XEPDB1

New Oracle connection as regular user

Test that the connection works, and save the connection.

You can now connect to this database and run SQL.

 

Login to System Accounts Correctly

I’ve had a few issues when attempting to login to the system accounts on my Oracle Express database before.

The errors have happened because I’ve entered the wrong information when trying to connect.

With Oracle Express, your system accounts need to connect as SYSDBA and also connect to the pluggable database (Service Name = XEPDB1).

As mentioned above, the connection details are:

  • Name: any name you want to use for your connection.
  • Username: SYS (or a user that has administrative rights)
  • Role: SYSDBA (if the username entered is SYSTEM)
  • Password: the password for this account
  • Host: the location of your database, such as “localhost”
  • Port: 1521 (the default)
  • Service Name: XEPDB1

New Oracle Connection as SYS

This should resolve any other issues you have when connecting to Oracle.

 

Passwords are Case Sensitive

In Oracle 12c, passwords were made case-sensitive. This means that a password of “february” is different from “FEBRUARY”.

This can be a bit of a surprise if you’re updating from the previous version of Oracle Express, which was 11g, and did not have case-sensitive passwords.

When you enter your password into the connection window, or when you create a user, be mindful of the case you are using.

 

Be Careful of _ORACLE_SCRIPT

Several suggestions online involve the user of a database parameter called _ORACLE_SCRIPT.

Let’s say you get this error message:

ORA-65096: invalid common user or role name
65096. 00000 -  "invalid common user or role name"
*Cause:    An attempt was made to create a common user or role with a name
that was not valid for common users or roles.  In addition to
the usual rules for user and role names, common user and role
names must start with C## or c## and consist only of ASCII
characters.
*Action:   Specify a valid common user or role name.

One of the more popular solutions online is to run this command, and then try again:

ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE;

This is a database session parameter that seems to resolve your issue.

However, it’s an undocumented feature. This means the behaviour of this is not supported and could change at any time.

Rather than using undocumented features, it’s better to follow the process above by creating a new user on the pluggable database. It takes a few more steps, but it’s the right way to do it, and you won’t see any strange side effects.

 

Conclusion

Connecting to Oracle databases can be confusing, especially if you’re just getting started with Oracle SQL and just want it to work. This post highlights a few common issues and resolutions to them.

If you have any other issues connecting to an Oracle database, let me know in the comments below.

31 thoughts on “How to Resolve Oracle Connection Issues”

      1. Ternia Pipkins

        Still getting the message:
        Status: Failure – Test Failed: IO Error: The Network Adapter could not establish the connection.

        Reply
      2. Salman Sk

        Sir, when I’m trying to create a new connection to the pluggable database it’s showing “Status: Failure-Test failed: Listener refused the connection with the following error:ORA-12514,TNS:Listener does not curr… ”
        Please tell me what should I do now:(

        Reply
        1. Ben

          Hi Salman, have you tried the resolutions in this guide? Is the Oracle database running? Have you entered the right connection details?

          Reply
          1. Salman Sk

            Ya I did so and it’s running…
            Okay, I’ll try again and then will come back to you again if it doesn’t work :)

            Thank you so much for your kind reply:)

  6. Bobin krishna

    Hi Ben,

    Appreciate your webpage on installing Oracle Database on Mac OS. I followed your steps and everything worked great and I was able to execute queries on Oracle Database from SQL Developer tool in my Mac OS.

    I am now trying to create JDBC applications and hence trying to connect to the Oracle database using Eclipse IDE and provided the same credentials as used in SQL developer but I face the below error. I use Eclipse 2020-12, Mac OS Catalina and Oracle database 19 enterprise version came with Developer VM. I am using Oracle thin driver Ojdbc8.jar. Connecting thru sql developer works like a charm. not sure whats the problem? your help would be appreciated and I am new to the DB world. Let me know if you need any more information.

    connection URL is jdbc:oracle:thin:@localhost:1521:orcl
    I used service_name not sid.

    java.sql.SQLException: Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:858)
    at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:793)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:57)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:747)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:562)
    at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.createConnection(JDBCConnection.java:328)
    at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBase.java:105)
    at org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:54)
    at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.open(JDBCConnection.java:96)
    at org.eclipse.datatools.enablement.internal.oracle.JDBCOracleConnectionFactory.createConnection(JDBCOracleConnectionFactory.java:27)
    at org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(ConnectionFactoryProvider.java:83)
    at org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(ConnectionProfile.java:359)
    at org.eclipse.datatools.connectivity.ui.PingJob.createTestConnection(PingJob.java:76)
    at org.eclipse.datatools.connectivity.ui.PingJob.run(PingJob.java:59)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
    Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
    ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

    at oracle.net.ns.NSProtocolNIO.negotiateConnection(NSProtocolNIO.java:284)
    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:340)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1600)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:591)
    … 14 more

    Reply
  8. Greeshma KR

    Hi Ben,

    My oracle database scripts are working fine on 11g (11.2.0.2). Recently, I’ve installed 18c Oracle database express edition. I’ve changed tns service as xepbd1. Then, it was connected properly. But, after execution some *.cmd/*.sql files, I got this error

    ERROR:
    ORA-01017: invalid username/password; logon denied
    Enter user-name:

    Please note that, the *.cmd/*.sql files are working fine on 11g. I understood that the 18c has container and pluggable databases. I have used pluggable database for this purpose.

    I’ve changed the tnsnames.ora file like this,

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

    Please help!

    Thanks.

    Reply
    1. Ben

      Hi, seems like a tricky problem. I am guessing it’s something to do with the pluggable database concept or the login being done on the container database (CDB) instead of the PDB. I can’t think of a solution off the top of my head but I have a feeling it’s to do with that.

      Reply
      3. Sangram

        Didnt understood. In my case only sys is able to login into pluggable database. While rest all user are getting error on Invalid username/password. Also the newly create user are not able to log in

        Reply
  10. Elsa

    Hi Ben,

    The connection to the new user is locked (my fault I entered the wrong password several times) but I’m not sure how I can unlock it. Could you please advise?
    (The Udemy course is fantastic by the way. Great job!)

    Reply
  11. Alejandra

    Hi Ben,

    I am working in a Mac, I have installed a VM and create the connection as you indicate in you video

    https://www.youtube.com/watch?v=ssef6bjPpZE

    I manage to connect to the DB with the parameters:

    username: hr
    password: oracle
    role: default
    conn type: basic
    hostname: 127.0.0.1
    port: 1521
    service name: orcl

    but once I try to create a new user I get this error:

    Error starting at line : 1 in command –
    CREATE USER alejandra IDENTIFIED BY alejandras
    Error report –
    ORA-01031: insufficient privileges
    01031. 00000 – “insufficient privileges”
    *Cause: An attempt was made to perform a database operation without
    the necessary privileges.
    *Action: Ask your database administrator or designated security
    administrator to grant you the necessary privileges

    Error starting at line : 2 in command –
    GRANT CONNECT TO alejandra
    Error report –
    ORA-01924: role ‘CONNECT’ not granted or does not exist
    01924. 00000 – “role ‘%s’ not granted or does not exist”
    *Cause: Either the role was not granted to the user, or the role did not exist.
    *Action: Create the role or grant the role to the user and retry
    the operation.
    …

    Why is that? Thanks for your priceless help

    Reply
    1. Ben

      Hi, I think this may be because the HR user does not have the privileges to create other users. If you want to create users, you may need to login as the SYS or SYSTEM user.

      Reply
  12. Bart

    Hi there, I just started your course on Udemy and have problems with creating a user, I am using the file you provided (with intro_user and mypassword like you said in video) and I am getting

    ORA-01017 error.

    I already established test connection but cannot make another one, I edited entries in a file user name and password too, did not work either.
    Please advice.

    Reply
    1. Ben

      That’s not good that you can’t log in. What’s the name of the service you’re connecting to? By this, I mean, did you select “SID” or “Service Name” on the New Connection window, and which value did you enter into the text field?
      Are you getting that error only for this new account?
      Was the password entered in all lower case, or was it mixed case?

      Reply
      1. Eli

        I’m having the same issue. I followed the steps on Udemy. Created the ‘Test’ connection first:
        Username: SYS
        Password: (xxxxxxxxxx)
        Role: SYSDBA
        localhost
        1521
        Service name: XEPDB1
        Then I opened the create_user file provided.
        Then I tried to add this connection:
        Username: intro_user
        Password: mypassword
        Role: default
        localhost
        1521
        SID: xe
        Throws username or password error.

        Reply
  13. saroj

    i followed all steps as mentioned. but i am getting error saying
    status: failure- test failed:io error:the network adapter could not establish the connection

    Reply
  14. Bennette McKnight

    Hi, Ben:

    Just started your “Oracle SQL – A Complete Introduction” class. Total newby. After installing “Oracle Database 21c Express Edition for Windows x64”, it shows a list of apps under “OraDB21Home1”

    * Database Configuration Assistant
    * Database Upgrade Assistant
    * Locale Builder
    * Net Configuration Assistant
    * Oracle Instance Manager
    * Oracle ODBC Help (has no info)
    * SQL Plus
    * Update Password
    * Wallet Manager

    I also installed “Oracle SQL Developer” and entered in the information for my first Connection and got the following error:

    “Status: Failure -Test failed: ORA-01017: invalid username/password; logon denied”

    I had created my account and verified so account is open.

    You indicate to make sure the Database is running and to click “Start” and then “Oracle Database”. But, as you can see in the list above, there is no app that says “Oracle Database”, or a “Get Started” link. I don’t know where to find it. The forum is over my head and my knowledge is probably too basic for them to even look.

    The installations were challenging, but I refuse to give up. Please help!

    Reply
    1. Ben

      Hi Bernadette, thanks for the comment! Oracle setup and connecting is the hardest part of working with Oracle, and it’s a shame because it’s the first thing we all need to do!
      Could you let me know what details you have entered for your connection (the hostname, username, whether you selected Service Name or SID, and the value you entered)? Feel free to email me directly if you prefer.

      Reply
  15. Bennette

    Hi, Ben:

    Thank you for your quick response.

    The details I entered are:

    Username: SYS
    Role: SYSDBA
    Hostname: localhost
    Port: 1521
    Service name: XEPDB1

    Part of my problem is not knowing if the Oracle Database is running. When installing SQL Developer, it indicated to make a shortcut and to always open the Developer with that shortcut. However, I am not asked to login; it automatically opens. How can I tell if the database is running? It is downloaded, but I don’t know where it is to “run”.

    Reply
  16. Bennette

    Hi, Ben:

    Just wanted you to know that I was able to resolve the issue. This issue had its pros and cons.

    CONS:

    I ultimately started over by uninstalling and then reinstalling the Oracle Database. I had downloaded the database before creating my Oracle account, in which I had planned to use the SAME password for the database and the account. However, when I created the Oracle Account, the password I had put in for the database did not meet the requirements for the Oracle account. By uninstalling and reinstalling the database, I realized I was putting the password for my Oracle Account and not the database. To eliminate that confusion, I made the password for the database the same as the account.

    PROS:

    As a newbie, because I was confused about the visibility of the database, I learned that the database is on the backend and the SQL Developer is on the front end. I learned there is a listener. I learned where to find the database to see if it is running. I found many sources of information to help me (including you). As a matter of fact, it was one of those sources that mentioned the password for the database which brought to my attention to start anew.

    Most of all, I can proceed.

    Thank you for taking the time out of your busy schedule to assist me. I can already see this is going to be an adventure. One I am willing to take.

    Reply
  17. Miracle

    Still getting the message:
    Status: Failure – Test Failed: IO Error: The Network Adapter could not establish the connection.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Table of Contents

→ Table of Contents