oracle-databasedblinktns

DBlink error when querying the remote table

I am creating DB link by using below command. I have not updated tnsnames.ora instead I used details in the commmand.

  CREATE DATABASE LINK test
  CONNECT TO apps IDENTIFIED BY xyzabc
  USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=awsss3270429.us.dell.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dell122de))';

The DBLink was created successfully. but

When I fired below query I got the error.

select * from USER_CS_SRS@test; . table `USER_CS_SRS` exist in remote database.

Error is attached.enter image description here

I am quite new to DB. Can anybody help me on the same.

Solution

  • You are missing a closing parenthesis at the end of your connection string. Your create statement should end:

    ...(CONNECT_DATA=(SERVICE_NAME=opm122de)))';
-----------------------------------------^

    Recreating what you posted:

    CREATE DATABASE LINK test
CONNECT TO apps IDENTIFIED BY xyzabc
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rws3270429.us.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=opm122de))';

select * from USER_CS_SRS@test;

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

    Adding the final ) changes that:

    drop database link test;

CREATE DATABASE LINK test
CONNECT TO apps IDENTIFIED BY xyzabc
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rws3270429.us.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=opm122de)))';

select * from USER_CS_SRS@test;

Error report -
SQL Error: ORA-12545: Connect failed because target host or object does not exist
...

    which is a different error; for me that's entirely reasonable because I can't resolve rws3270429.us.oracle.com, never mind reach it. As your profile says you work for Oracle, presumably you can - and this either works or throws a different error. Either way the original ORA-12154 error has been fixed.