How do I list tnsnames

Question

Is there any easy way to list out all the available tnsnames in my system via command line?

Whenever I need to find out a tnsname, I just simply search for the tnsnames.ora file with tnsping command and open it in a text editor to scan through. Then subsequently run tnsping [tnsname] to check the connection health. I was thinking if there's any easy command like tnslist or tns -l to list out all the tnsnames but I couldn't find such.

Answer 1

Assuming that you have such a tnsnames.ora file :

DB01 = 
    (DESCRIPTION = 
        (FAILOVER=off) 
        (LOAD_BALANCE=off) 
          (ADDRESS = (PROTOCOL = TCP)(HOST = db01-vip)(PORT = 1521)) 
         (CONNECT_DATA = 
             (SERVER = DEDICATED) 
             (SERVICE_NAME = mydb1) 
         ) 
     ) 

DB02 = 
    (DESCRIPTION = 
        (FAILOVER=off) 
        (LOAD_BALANCE=off) 
          (ADDRESS = (PROTOCOL = TCP)(HOST = db02-vip)(PORT = 1531)) 
         (CONNECT_DATA = 
             (SERVER = DEDICATED) 
             (SERVICE_NAME = mydb2) 
         ) 
     ) 

edit your .profile or .bash_profile like this:

[oracle@mydb12c~ ] vi .bash_profile

ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1; export ORACLE_HOME
...
alias lstns="sed -n '/DESCR/{x;p;d;}; x' $ORACLE_HOME/network/admin/tnsnames.ora | sed "s/=/${s}/""
echo 'lstns : tnsnames.ora listing'

[oracle@mydb12c~ ] . .bash_profile

lstns : tnsnames.ora listing

[oracle@mydb12c~ ] lstns

  DB01 
  DB02

Answer 2

There is no tnsnames.ora file listing command. But there are several options where the tnsnames.ora file can be placed. You can only use the find command The priority of searching for tnsnames.ora files by the oracle client.

    1) current directory (Linux, Windows)
    2) $TNS_ADMIN (Linux, Windows environment variable, Windows registry key)
    3) $ORACLE_HOME/network/admin (Linux, Windows)
    4) /etc (Linux)
    5)  %USERPROFILE%/AppData/Oracle ( Windows 7). 


[root@elbrus-1 ~]#  find / -name tnsnames.ora
/opt/ora/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
/opt/ora/app/oracle/product/11.2.0/dbhome_1/network/admin/samples/tnsnames.ora
/home/oracle/diman/tnsnames.ora
[root@elbrus-1 ~]#