How To Find Oracle Database Service Name

Oracle sid & service_name tips

Oracle Database Tips by Donald BurlesonDecember 5, 2015

Question: What is the difference between the SID and SERVICE_NAME in my tnsnames.ora file? Why does Oracle take three parameters for the aforementioned thing, instance_name, service_name, db_name, and oracle_sid?

Answer: Part of the reason for this separation is in the evolution of Oracle, simply we need to remember that Oracle specifies database locations in many files. For TNS networking, the SERVICE_NAME and SERVICE_NAMES parameters were first introduced in Oracle 8i and supplant the older SID designation.

Difference between SID and SERVICE_NAME

Annotation the subtle difference between a SID and a SERVICE_NAME. The SID is the aforementioned as the $ORACLE_SID surroundings variable in the sense that the SID is the organisation is for a unique case on the server.

On the other hand, the SERVICE_NAME is used to register an case with the listener. In almost all cases, Oracle recommends that the value of the service_name exist the same as the SID. Nonetheless, a SERVICE_NAME tin can point to more than than one example, and the DBA can gen-in additional SID's into a SERVICE_NAME .

Also run into my TNS-12502 Tips.

MOSC Note 68632.1 verifies that the SERVICE_NAMES specifies one or more than names for the database service to which this instance connects. Y'all can also specify multiple service_name entries in gild to distinguish among different uses of the same database.

Hither is an instance if two tnsnames.ora files, one using SID and the other using SERVICE_NAME:

london =
(DESCRIPTION =
     (ADDRESS_LIST =
         (ADDRESS =
           (Customs = TCP)
           (PROTOCOL = TCP)
          (HOST = 123.45.6789)
           (PORT = 1521)
          )
      )
     (CONNECT_DATA =
        (SERVER=dedicated)
        (SERVICE_NAME = london)
    )

berlin =
(DESCRIPTION =
     (ADDRESS_LIST =
         (Accost =
           (Community = TCP)
           (PROTOCOL = TCP)
          (HOST = hun)
           (PORT = 1521)
          )
      )
     (CONNECT_DATA = (SID = kraus))
    )

db_name - This is created when y'all issue your "create database" statement and it can exist seen in the v$database view.

SQL> CREATE DATABASE mysid DATAFILE SIZE 400M
2>   DEFAULT TEMPORARY TABLESPACE temp_ts
three>   TEMPFILE SIZE 10M
four>   UNDO TABLESPACE undo_ts DATAFILE SIZE 10M;

select
proper noun,
value
from
v$parameter
where
name = 'db_name';
Proper noun VALUE
------- ----------------------------------------------
db_name mysid

instance_name - In the init.ora file

init.ora
INSTANCE_NAME=mysid

select
instance_number,
instance_name,
host_name
from
v$case
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
--------------- ---------------- ----------------------1 mysid MYSERVER

service_name - The service_name is in the tnsnames.ora file to allow you to annals an instance with the listener:

mysid=
(Clarification =
(Address =
(PROTOCOL = TCP)
      (HOST = myserver)
      (PORT = 1521))
)
(CONNECT_DATA =
      (SERVICE_NAME = mysid)
   )
)

sid_name - We find the sid_name values listed in the listener.ora file:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
      (SID_NAME = mysid)
      (ORACLE_HOME = /u01/app/oracle)
)
(SID_DESC =
(GLOBAL_DBNAME = remote.dba.net)
(ORACLE_HOME =/u01/app/oracle/product/10.1.2)
      (SID_NAME = mysid)
       )
)
)

global_name - If you are using global_names (global_names = TRUE), the global_name is the combination of the db_name and db_domain:

select *
from
global_name;
GLOBAL_NAME
---------------------------------------
MYSID.REMOTE.DBA.Cyberspace

For a complete clarification of these values, see Steve Karam's book "Easy Oracle Jumpstart":

Get the Complete
Oracle SQL Tuning Information

The landmark volume "Advanced Oracle SQL Tuning The Definitive Reference" is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and yous can buy it for 30% off directly from the publisher.