Cannot connect with database link in Oracle

Question

I have three Oracle databases connections (XE, XE_HR and XE_SBD_HR).

I've tried to create private database link with fixed user that connects as HR using HR password with below query on XE_SBD_HR:

CREATE DATABASE LINK DATABASE_LINK_1 CONNECT TO HR IDENTIFIED BY HR USING 'HR';

But now when I want to get data from table:

SELECT * FROM COUNTRIES@DATABASE_LINK_1;    

SQL Error: ORA-12154: TNS:could not resolve the connect identifier

I think this is caused by wrong database link connections because of this query response in XE_SBD_HR:

COMMIT;  
ALTER SESSION CLOSE DATABASE LINK DATABASE_LINK_1;

ORA-02081: database link is not open

How to fix that?

wolφi · Accepted Answer · 2018-06-16 19:00:43Z

2

You might have more success if you specify the hostname, port and service name or Oracle SID in the connect string, for instance

CREATE DATABASE LINK DATABASE_LINK_1 CONNECT TO HR IDENTIFIED BY HR USING 'localhost:1512/XE_SBD_HR';

answered Jun 16, 2018 at 19:00

wolφi

8,3814 gold badges42 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

smiarooo Over a year ago

After executing your query and trying to SELECT there is a new obstacle: SQL Error: ORA-12541: TNS:no listener.

wolφi Over a year ago

You need to replace my example with your actual values. How do you connect to the database XE_SBD_HR? Please show us the connection string or command you are using (without the password of course).

smiarooo Over a year ago

I don't know if you mean this - link. In listener.ora file my port is set to 1521, when switched to this value in query got: SQL Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.

wolφi Over a year ago

Thanks for the picture. You use connection type=TNS. That means you use a file called tnsnames.ora. What's the entry in it for the name XE?

smiarooo Over a year ago

XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-NAME)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE))) - that's what I've found.

|

Collectives™ on Stack Overflow

Cannot connect with database link in Oracle

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related