Oracle connection methods (SERVICE_NAME vs SID)¶

When configuring a connection to an Oracle database, it is critical to distinguish between the SERVICE_NAME and the SID (System Identifier). These two parameters represent different ways of identifying the target database instance within the tnsnames.ora file or connection string.^[600-developer__database__oracle__oracle-plsql.md]

SERVICE_NAME¶

The SERVICE_NAME is used to identify a specific service registered with the listener.^[600-developer__database__oracle__oracle-plsql.md] In a tnsnames.ora configuration, this parameter is defined within the CONNECT_DATA block of the description.

DB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.xxx.xx.xx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sdb1)
    )
  )

SID¶

The SID is a unique name that distinguishes a specific database instance from other instances on the same system.^[600-developer__database__oracle__oracle-plsql.md] Like SERVICE_NAME, it is configured within the CONNECT_DATA section, but uses the (SID = ...) syntax.

DB2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.xxx.xx.xxx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = sdb2)
    )
  )

Connection Formats¶

A standard connection string typically combines the hostname, listener port (default 1521), and the identifier.^[600-developer__database__oracle__oracle-plsql.md]

• Format: HOST:PORT/SERVICE_NAME or HOST:PORT:SID
• Example: 192.168.79.100:1521/xe (where xe often refers to the service name or SID of an Oracle Express Edition instance).^[600-developer__database__oracle-plsql.md]

Sources¶

^[600-developer__database__oracle__oracle-plsql.md]