SID is a
unique name for an Oracle database instance. To switch between Oracle
databases, users must specify the desired SID. The SID is included in the
CONNECT DATA parts of the connect descriptors in a TNSNAMES.ORA file, and in
the definition of the network listener in the LISTENER.ORA file. Also known as
System ID.
Oracle Service Name may be anything descriptive like "MyOracleServiceORCL". In Windows, You can your Service Name running as a service under Windows Services
SERVICE_NAME
is the new feature from oracle 8i onwards in which database can register itself
with listener. If database is registered with listener in this way then you can
use SERVICE_NAME parameter in tnsnames.ora otherwise - use SID in tnsnames.ora. Also if you
have OPS (RAC) you will have different SERVICE_NAME for each instance.
SERVICE_NAMES
specifies one or more names for the database service to which this instance
connects. You can specify multiple services names in order to distinguish among
different uses of the same database. For example:
SERVICE_NAMES
= mybasicknowledge.mydomain.com, mybasicknowledge2.mydomain.com