what differences SID and Service Name when use Oracle SQLDeveloper?

Question

English is not my native please understand

When I try to login like system/password as sysdba to oracle(linux) by sqldeveloper(window)

It fail when through Service Name and return ORA-0131 :insufficient privileges

but SID? successfully passed

what differences? and how to connect with Service Name?

Answer 1

Thomas Kyte explained the difference beatifully :

A service name is more flexible than a SID would be.

A database can dynamically register with a listener using one or more service names. In fact, more than one database can register with a listener using the same service name (think about a clustered environment where you have multiple instances that all are the same database under the covers).

A database on the other hand has a single SID. And a single SID goes to a single database. It is a pure 1:1 relationship.

A service is a many to many relationship.

Service names are used with dynamic registration - the data registers with the listener after it starts up. Once it does that, you can connect.

With the SID - that is more like telling the listener "I want you to connect to this specific database, I know the 'address', here you go"

With the SERVICE - you are asking the listener to put you in touch with a database that can service your request, a database that registers using that service.

More information here, https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1508737000346067364

Similar question was asked in Stack Overflow 6 years back, How SID is different from Service name in Oracle tnsnames.ora

In your test environment, play around with tnsnames.ora and listener.ora. AFAIK, from 12c, Oracle uses only service_name, since it is flexible and has many to many relationship. I will cross-verify from documention if I find it incorrect.