Being an Oracle Database Administrator or Developer, you should understand vital components and concepts of the Oracle Database. One such concept of the Oracle Database environment is Oracle SID and SERVICE NAME. Many people are confused about them, so let’s discuss the following points to understand them clearly:
- Oracle “SERVICE NAME”
- Oracle “SID”
- Difference Between “SID” and “SERVICE NAME”
- Find “SERVICE NAME” Using SQL PLUS
- Find “SID” Using SQL PLUS
Oracle “SERVICE NAME”
The SERVICE NAME is a feature that was first introduced in Oracle 8i that lets a database register itself with the listener. It is a TNS alias that is utilized by clients in the Oracle Database environment for connecting to a specific service in single or multiple instances.
Oracle “SID”
Oracle SID is an acronym for System Identifier, which is a unique name given to a specific database instance for its identification by Operating System. Every database instance must have a SID which is specified at the time of the creation of the database instance.
Differences between “SID” and “SERVICE NAME”
There are a few major differences between SID and SERVICE NAME in Oracle Database Environment. Let’s enlist them below:
- Every single database has a unique SID whereas each database instance can have single or multiple services with different SERVICE NAMES.
- The SID is set at the time of the creation of the database instance which cannot be changed afterwards. On the other hand, SERVICE NAMES can be configured while the instance is running state.
- SID identifies the database instance while connecting to the database from the operating system or remote clients whereas, SERVICE NAME identifies the services offered by the database instance while connecting to the database from remote clients.
Note: The SID and Service Name can be the same in some cases by default which can be changed later on.
Find “SERVICE NAME” Using SQL PLUS
To find the service name in Oracle, log in to your database using SQL PLUS utility or Command Prompt. For this example, SQL PLUS is used, so execute this command to see the service name:
The output displays the service name:
You have learned the command for displaying the service name, let’s now see the command for finding the SID.
Find “SID” Using SQL PLUS
To find the SID, login to the database using SQL PLUS and run this command:
The output will display the SID, which is a unique name for the database instance:
You have learned about Oracle SIDs and SERVICE NAMES along with the difference between them and the commands to find them.
Conclusion
SERVICE NAME and SID are essential identifiers, SID is used to identify Oracle database instances uniquely and SERVICE NAME identifies single or multiple services offered by these instances. SID is defined at the time of the creation of the database which cannot be changed later on. On the other hand, SERVICE NAMES are configurable parameters that can be modified while the instance is running state. This post discussed the difference between Oracle SIDs and SERVICE NAMES.
