No doubt this is incredibly old hat and I've just missed it somewhere along the line, but with a nod and a wink from Andrew Fraser at work and using my more stable server connection, here's a small thing I didn't know.
On Unix and Linux, you can have a personal .tnsnames.ora (note the leading dot) file in your home directory that over-rides the tnsnames.ora files in the usual locations.
[oracle@ISP4400 ~]$ cd ~
[oracle@ISP4400 ~]$ cat .tnsnames.ora
DOUG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST92)
)
)
TEST1020 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST1020)
)
)
[oracle@ISP4400 ~]$ echo $TNS_ADMIN
[oracle@ISP4400 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TEST1020 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST1020)
)
)
TEST92 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST92)
)
)
So I have DOUG as an alias to the 9.2 database and TEST1020 in my personal .tnsnames.ora , plus TEST92 (which points to the same instance as DOUG) and TEST1020 in the tnsnames.ora in $ORACLE_HOME/network/admin
I can connect to all three aliases :-
[oracle@ISP4400 ~]$ sqlplus testuser/testuser@DOUG
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 15 20:11:21 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[oracle@ISP4400 ~]$ sqlplus testuser/testuser@TEST92
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 15 20:11:26 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[oracle@ISP4400 ~]$ sqlplus testuser/testuser@TEST1020
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 15 20:11:32 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
But TEST1020 is in both tnsnames files, so what happens if there's an error in the definition in .tnsnames.ora? I'll point it to a non-existent service.
[oracle@ISP4400 ~]$ cat .tnsnames.ora
DOUG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST92)
)
)
TEST1020 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ISP4400.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DUMMY)
)
)
[oracle@ISP4400 ~]$ sqlplus testuser/testuser@TEST1020
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 15 20:13:31 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name:
My point is that, if you don't know that the user has their own .tnsnames.ora file in their home directory, you would go and look at the copy in TNS_ADMIN in $ORACLE_HOME/network/admin and the TNS entry would look fine. That could be confusing.
