Skip to content

TNSNAMES, How and Why

  • by

Local Naming = TNSNAMES

Whenever we need to connect to the database, we usually provide a connection string like this:

SQL> conn username/password@chicago-db-server:1521/ORCL
Connected.

Or we can provide the full connect descriptor.

SQL> conn username/password@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=chicago-db-server)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)))"
Connected.

As we can see, the connection string looks lengthy and error prone. Luckily, we have Oracle Net Local Naming that can simplify it.

Local naming is an alias system conducted by tnsnames.ora file, each alias is a connect identifier which represents a long, complex connection description including destination, port, service name, SID, etc.

An example of connect identifier would look like this:

CHICAGO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = chicago-db-server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

In the above, CHICAGO, the connect identifier represents the connection details behind the equals sign. That is to say, you don't have to use full connect descriptor to connect to the database, you can use the alias directly. Connection tools like SQL*Plus will lookup its content in tnsnames.ora at real-time. For example:

SQL> conn username/password@chicago
Connected.

It's simple and clean.

Now the question is, where is tnsnames.ora in your computer system?

Leave a Reply

Your email address will not be published. Required fields are marked *