sqldatabaseoracle-databasedatabase-link

Cannot connect with database link in Oracle

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?

Solution

  • 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';