What is sid in Oracle
Oracle SID is the unique name that uniquely identifies your instance/database, whereas the Service name is the TNS alias that you give when you remotely connect to your database, and this Service name is recorded in tnsnames.ora file on your clients, and it can be the same as SID, and you can also give it any other name you want.
What is SERVICE_NAME in Oracle
SERVICE_NAME is the new feature from oracle 8i onwards in which the database can register itself with the listener. If the database is registered with the listener in this way, then you can use the 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 to distinguish among different uses of the same database.
For example:
SERVICE_NAMES = sales.acme.com, widgetsales.acme.com
You can also use service names to identify a single service that is available from two different databases through the use of replication.
In an Oracle Parallel Server environment, you must set this parameter for every instance.
How to find "SID" and "Service Name" in oracle
Find Oracle "SERVICE NAMES"
Oracle Servie name is similar to Oracle Instance Name,
Suppose Oracle instance name is "prdserv" then oracle "service name" will be "oracleserviceprdserv"
Get Oracle “SERVICE NAMES” through SQL commands
Connect to the server as "system" using SID:
sql> select value from v$parameter where name like '%service_name%';
OR
sql> show parameter service_name;
OR
sql> select sys_context('userenv','service_name') from dual;
Get Oracle Instance Name
sql> SELECT sys_context('userenv','instance_name') FROM dual;
Get Oracle SID
sql> SELECT sys_context('USERENV', 'SID') FROM DUAL;
Get Oracle Database Name
sql> select ora_database_name from dual;