DBMS_TNS package provides the RESOLVE_TNSNAME function to resolve a TNS name and return the corresponding Oracle Net8 connection string.When you connect locally to the server (no SQL*Net, no listener), the Oracle session inherits the client environment.   It is available on oracle 12.2 Release . The DBMS_TNS package contains one function, RESOLVE_TNSNAME, which returns the resolved connect string from configured sources that have been defined in the names.directory_path parameter in the sqlnet.ora file.

SQL> desc dbms_tns
FUNCTION RESOLVE_TNSNAME RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TNS_NAME VARCHAR2 IN

you can run it when connected to the database to see how the name is resolved

[oracle@Primary ~]$ sqlplus system@pdb1

SQL> select dbms_tns.resolve_tnsname('&_connect_identifier') from dual;
old 1: select dbms_tns.resolve_tnsname('&_connect_identifier') from dual
new 1: select dbms_tns.resolve_tnsname('pdb1') from dual

DBMS_TNS.RESOLVE_TNSNAME('PDB1')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdb1.aeetisalat12345.oraclecloud.internal)(CID=
(PROGRAM=oracle)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(USER=oracle))))

When we switch the container inside the CDB then it will show only container tns entry.

[oracle@Primary ~]$ sqlplus / as sysdba

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> select dbms_tns.resolve_tnsname('&_connect_identifier') from dual;

old 1: select dbms_tns.resolve_tnsname('&_connect_identifier') from dual
new 1: select dbms_tns.resolve_tnsname('ORCL') from dual

DBMS_TNS.RESOLVE_TNSNAME('ORCL')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL.aeetisalat12345.oraclecloud.internal)(CID=
(PROGRAM=oracle)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(USER=oracle))))

SQL> alter session set container=pdb1;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO

SQL> select dbms_tns.resolve_tnsname('&_connect_identifier') from dual;
old 1: select dbms_tns.resolve_tnsname('&_connect_identifier') from dual
new 1: select dbms_tns.resolve_tnsname('ORCL') from dual

DBMS_TNS.RESOLVE_TNSNAME('ORCL')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL.aeetisalat12345.oraclecloud.internal)(CID=
(PROGRAM=oracle)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(USER=oracle))))

If we want to check all database links are going

SELECT DB_LINK, DBMS_TNS.RESOLVE_TNSNAME(HOST) FROM DBA_DB_LINKS;

DB_LINK DBMS_TNS.RESOLVE_TNSNAME(HOST)
-------------- --------------------------------------------------------------------------------
PRIM (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL.aeetisalat12345.oraclecloud.internal)(CID=
(PROGRAM=oracle)(HOST=Primary.compute-aeetisalat12345.oraclecloud.internal)(USER=oracle))))

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.