5

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.

Damith
  • 417
  • 1
  • 5
  • 15

2 Answers2

3

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
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • 1
    Thanks much. We shall have similar .bat file in Windows as well and put it in the system path. So that, we shall run the command from terminal seamlessly. I initially thought there is a one bundle with Oracle. – Damith Sep 27 '17 at 11:45
0

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 ~]#
Dmitry Demin
  • 2,006
  • 2
  • 15
  • 18
  • 1
    that isn't his question – Ravi Sep 27 '17 at 04:15
  • @Dmitry Demin, Thanks for the reply but what I want to know is how to list tnsnames. – Damith Sep 27 '17 at 04:22
  • 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. – Dmitry Demin Sep 27 '17 at 04:26
  • I don't think `%USERPROFILE%/AppData/Oracle` is any default directory for `tnsnames.ora` file. Most likely this is inherited from `TNS_ADMIN` setting. Do you have any reference for this list? See also https://stackoverflow.com/questions/28280883/determining-locatation-of-relevant-tnsnames-ora-file/28283924#28283924 for more information. – Wernfried Domscheit Sep 27 '17 at 09:51