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))))