How to get TNS connect strings (like tnsping) to check a dblink via a session

dblinkoracletnsnames

I would like to know, if there is any chance to get the currently used TNS connect string, that will be used for a DB-Link from a db-server.

I'm connected to a schema on a database located on any server.
Now, I have some database links, using tnsnames and which are currently not working properly.
I would like to check, which target database is behind this identifier to verify easily, if this can be correct.

If I would have OS access to the db-server, I would be able to read the ./tnsnames.ora or using tnsping.

But the DDL of the dblink just uses the TNSNAME (RDBX in this example):

CREATE DATABASE LINK "ABC"
CONNECT TO "RUSER" IDENTIFIED BY VALUES ':1'
USING 'RDBX';

Of course, I could ask a DBA, but this is not always as easy in our environment and I would like to be able to double-check it myself without having OS-access.

Best Answer

I do not think that is possible. Someone or something needs access to the Filesystem to read the tnsnames.ora. When using the DB-link oracle will get the definition for RDBX from the tnsnames.ora file. This is transparent to the user, so the user will not notice. As @EdStevens suggested I would try to solve the political issues and work with the DBA. Or, ask the OS-admin to have a look for you.

Related Solutions

How to use sqlplus to connect to an Oracle Database located on another host without modifying the own tnsnames.ora

 sqlplus user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))

Maybe, and this might be dependant on the command line environment you're using, you need to quote the string, something like

 sqlplus "user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))"

 sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))'

Error creating linked database to oracle

In a database link, you should use the net_service_name, i.e. the OPT string, instead of SERVICE_NAME identifier.

EDIT:

Example:

cat tnsnames.ora|grep TEST
TESTNS=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=THE_HOST)(Port=THE_PORT)))(CONNECT_DATA=(SID=THE_SID)))

oracle@hostname> export ORACLE_SID=ANOTHER_SID
oracle@hostname> sqlplus username
Enter password: 
Connected to:
<few lines of sqlplus banner>
SQL> create database link lll connect to <the_username> identified by <the_password> using 'testns';
Database link created.
SQL> select * from global_name@lll;

GLOBAL_NAME
--------------------------------------------------------------------------------
<the_global_name>

SQL>

Best Answer

Related Solutions

How to use sqlplus to connect to an Oracle Database located on another host without modifying the own tnsnames.ora

Error creating linked database to oracle

Related Question