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