Skip to content
Home » TNSNAMES, How and Why

TNSNAMES, How and Why

Local Naming = TNSNAMES

Whenever we need to connect to the database, we usually provide a connection string by easy connect 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.

tnsnames.ora

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

A TNS entry including connect identifier and connect descriptor in tnsnames.ora file may 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 descriptor behind the equals sign.

For normal users, they can use NETCA to add a TNS entry graphically.

That is to say, you don't have to use full connection string (connect descriptor) to connect to the database, you can use the alias (connect identifier) directly.

Connection tools like SQL*Plus will go for tnsnames.ora and lookup its content by the alias 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 *