ORA-12514 Cause and Solution

Are you getting an “ORA-12514: TNS:listener does not currently know or service requested in connect descriptor” error? Learn what causes it and how to resolve it in this error.

ORA-12514 Cause

If you try to connect to an Oracle database, you may receive this error:

ORA-12514: TNS:listener does not currently know or service requested in connect descriptor

This error means that a listener on the Oracle database has received a request to establish a connection. However, the connection descriptor mentions a service name that has not been registered with the listener, or has not been configured for the listener.

 

ORA-12514 Solution

There are a few steps to resolve this error.

1. Check the service names on the database (if possible)
2. Update TNSNAMES.ORA to include the service name

 

Step 1: Check the service names on the database

The first step to resolve the ORA-12514 is to see which service names the database knows about.

If you’re able to connect to your database, run the following query:

SELECT value
FROM v$parameter
WHERE name = 'service_names';

VALUE

XE

This will show all of the service names your database knows about.

You’ll need to add these into the TNSNAMES.ORA file, which I’ll show you how to do shortly.

What if you can’t connect to your database? This is a valid question, seeing as you’re getting this error when you try to connect to the database.

You could use a different connection string.

Or, you could connect to the server and run sqlplus locally.

Or, you could ask a coworker or a DBA to run this command for you.

This all depends on how your environment is set up.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

 

Alternative: check which service names are known by the listener

Another way of seeing which service names are available to the listener is by running the lsnrctl command

lsnrctl services

This will show you the names of the services, which you can check against your connect descriptor. It’s an alternative way to getting the service names if you can’t connect to the database as mentioned in the previous step.

 

Step 2: Update your TNSNAMES.ORA file

After you have the name of the service, open your TNSNAMES.ORA file.

This file is located here:

%ORACLE_HOME%\NETWORK\ADMIN\

ORACLE_HOME is where your Oracle database is installed on the server, or on your own computer if you’re using Oracle Express.

For example, in my installed version of Oracle Express, my ORACLE_HOME is:

C:\oraclexe\app\oracle\product\11.2.0\server

So, my TNSNAMES is located here:

C:\oraclexe\app\oracle\product\11.2.0\server\network\admin

Your TNSNAMES.ORA file has one or more entries in it that represent your databases. They are in the following format:

<addressname> =
(DESCRIPTION =
  (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
  )
(CONNECT_DATA =
  (SERVICE_NAME = <service_name>)
)
)

The addressname is what you use in the connection string, and service_name is what the service is known as on the database (from step 1)

Now, update the TNSNAMES.ORA by either adding a new entry or modifying an existing entry. You need to make sure that there is an entry that has a service_name value that is equal to the value you found in step 1.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Ben-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

Once you have done this, you should be able to connect to your database in the method you wanted to that caused this error.

sqlplus user@XE

This should now work.

 

So, that’s how you resolve the ORA-12514 error.

You can read my guide to the Oracle errors here to find out how to resolve all of the Oracle errors.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: