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