First installed oracle home is default home! The next installed will work with that installed tools . For example
Oralce 8.0.6 ---
sqlplus will use tnsnames from this home.
If you installed oracle 8.1.6 and oem then oem work sheet will use tnsnames from the new home. I got some funny problem when i install db artisan. When i select database names from tnsnames.ora in this tool it selected from old home. But if i selected the names, I got tnsnames error. Then i directly i enterd a connect string name from new oracle home tnsnames.ora and it is connected. So when it's creating the data source it's bringing information from old home, But when it's connecting to database it's looking into new home tnsnames.ora. Any way one shortcut is create 2 connect strings and put each one in each tnsnames.ora
service_a :sid =database1
service_b :sid =database2
Put service_a in old home tnsnames.ora and
service_b in new home tnsnames.ora and find out which one is working.
Other why's put same entry's in all tnsnames.ora.
Check Path in env.
For Release2 (8.1.6) tnsnames.ora will be read in the following order:
1. current working dir
2. (/etc/tnsnames.ora for HP and /var/opt/oracle/tnsnames.ora for SUN)
4. default dir, ie, $ORACLE_HOME\network\admin
in addition to cmai:
on NT: TNS_ADMIN registry key IN HOME of the product(net8) used to connect by client app. Net8 used to connect depends on tool: some tools are quite smart in selecting net8 library to use.