ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Error
Error occured while tried to access the SQL query of DBLINK.
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

SELECT sales_item
FROM sales_north@SALE_DBLINK
WHERE sales_id = 785;

FROM sales_north@SALE_DBLINK
*
ERROR at line 2:
ORA-12504: TNS:listener was NOT given the SERVICE_NAME IN CONNECT_DATA

Cause
service entry not exists in the TNSNAMES.ora file or may it has wrong entry.

Solution
1. Check the TNSNAMES.ORA file exists.

2. Check the tns entry present in the TNSNAMES.ORA file.

SALESDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SALESDB)
)
)

3. Check DB link is present in the Database.

select * from USER_DB_LINKS

Following are example of creating SALESDB link use in above example:

CREATE DATABASE LINK SALE_DBLINK
CONNECT TO SALEUSER IDENTIFIED BY salespassword
USING 'SALESDB';

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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