oracle-databasetnsnamestnsping

How do I list tnsnames


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.


Solution

  • 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