2

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?

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Vert
  • 29
  • 1
  • 1
  • 7
  • No clue what you are asking. Please show the commands you are trying to execute, etc. – OldProgrammer Sep 21 '14 at 13:19
  • `sysdba` role is not granted to the `system` account by default. When you specify `SID`(client and server on same computer) and connect as `sysdba` you will use OS authentication - oracle username and password will be ignored as long as OS user in in `DBA` group. When you use `service name` and connect as `sysdba` the password file will be used and if `sysdba` role is not granted to that user(user is not present in the password file), you'll get the `ORA-0131` error. – Nick Krasnov Sep 21 '14 at 15:21
  • Thanks for all of your answers – Vert Sep 22 '14 at 17:53

1 Answers1

5

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.

Community
  • 1
  • 1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Thanks @Glenn for the edit. Indeed it is ***many to many relationship in general***. But, as a conclusion, I said one to many specifically for OP's case. – Lalit Kumar B Sep 21 '14 at 15:48
  • wow Thanks @Lalit Kumar B But I don`t use tnsnames.ora and listener.ora I didn`t install Oracle Client – Vert Sep 22 '14 at 17:51
  • Regarding `tnsnames.ora` and `listener.ora`, it depends, whether you are conneting to the database locally or over the network. – Lalit Kumar B Sep 22 '14 at 17:54
  • Really? Isn`t it sqldeveloper uses jdbc?? – Vert Sep 22 '14 at 18:06