How to Resolve Oracle Connection Issues
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 Oracle courses 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
1ORA-65096: invalid common user or role name
265096. 00000 - "invalid common user or role name"
3*Cause: An attempt was made to create a common user or role with a name
4that was not valid for common users or roles. In addition to
5the usual rules for user and role names, common user and role
6names must start with C## or c## and consist only of ASCII
7characters.
8*Action: Specify a valid common user or role name.
IO Error
1Status : Failure - Test failed: IO Error :
2The network Adaptor could not establish the connection.
User Does Not Exist
1ORA-01917: user or role 'INTRO_USER' does not exist
21917. 00000 - "user or role '%s' does not exist"
3*Cause: There is not a user or role by that name.
4*Action: Re-specify the name.
SID Unknown
1ORA-12505 error. TNS: listener doesn't know of SID given in connect descriptor.
Invalid Username/Password
1ORA-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.
- Ensure the Oracle Database is running
- Use the correct SID or Service Name for Pluggable Databases
- Login to the system accounts correctly
- 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:
- Go to Start > Oracle
- Click on Start Oracle Database
- 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:
1ORA-65096: invalid common user or role name
265096. 00000 - "invalid common user or role name"
3*Cause: An attempt was made to create a common user or role with a name
4that was not valid for common users or roles. In addition to
5the usual rules for user and role names, common user and role
6names must start with C## or c## and consist only of ASCII
7characters.
8*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)
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.
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:
1CREATE USER intro_user IDENTIFIED BY mypassword;
2GRANT CONNECT TO intro_user;
3GRANT CREATE SESSION, GRANT ANY PRIVILEGE TO intro_user;
4GRANT UNLIMITED TABLESPACE TO intro_user;
5GRANT 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
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
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:
1ORA-65096: invalid common user or role name
265096. 00000 - "invalid common user or role name"
3*Cause: An attempt was made to create a common user or role with a name
4that was not valid for common users or roles. In addition to
5the usual rules for user and role names, common user and role
6names must start with C## or c## and consist only of ASCII
7characters.
8*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:
1ALTER 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.