0

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?

smiarooo
  • 3
  • 2

1 Answers1

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';
wolφi
  • 8,091
  • 2
  • 35
  • 64
  • After executing your query and trying to SELECT there is a new obstacle: SQL Error: ORA-12541: TNS:no listener. – smiarooo Jun 16 '18 at 19:08
  • 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). – wolφi Jun 16 '18 at 19:10
  • I don't know if you mean this - [link](https://ibb.co/fV7cEd). 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. – smiarooo Jun 16 '18 at 19:25
  • 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`? – wolφi Jun 16 '18 at 19:30
  • XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-NAME)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE))) - that's what I've found. – smiarooo Jun 16 '18 at 19:35
  • Then a database link with `... USING 'DESKTOP-NAME:1521/XE'` should work. But `DESKTOP-NAME` shoud be the hostname of the computer where the database listener is running on. Is yours called like that? – wolφi Jun 16 '18 at 19:38
  • I've just removed random string in desktop name, now works perfect after your using modification, today you are my hero! :) – smiarooo Jun 16 '18 at 19:41
  • You can use short names in the `USING` clause, but the are looked up in the `tnsnames.ora` file of the *database server* where the link is created, which is normally in the same directory as the `listener.ora`. – wolφi Jun 16 '18 at 19:50