0

I wanted to form oracle database URL for my JDBC conection and few websites say SID should be part of URL and few say schema name.

Please compare schema, service name and SID in ORACLE? And how they are related?

is it like SID:schema is 1:n as in one SID can point to n schemas?

Please explain with the relation between them.

sAnS
  • 1,169
  • 1
  • 7
  • 10
Savita
  • 73
  • 2
  • 10

2 Answers2

0

A SID is the unique identifier for a database, or if it's a RAC system for an instance of a database.

A Service is an identifier for a service offered by the database, and the database should be configured for services such as "BILLING_APP" or "CUST_WEBSITE". http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams217.htm

Don't confuse a database and an instance, by the way. A database is the set of data and control (etc) files, the instance is the memory areas and processes that access it. RAC has multiple instances per database, each of which can register with the listener to offer all or a subset of the total services offered by the database.

A schema is effectively the username that owns the objects, and there can be multiple schemas in the database.

So an application should be referencing a SERVICE to connect to, through a listener on a certain host and port. The listener resolves this to a database (possibly one of many that register to offer that service). The connection is made to the database as a user who might or might not be the owner of the schema. Typically the connection is not made as the schema owner for security reasons -- in fact the best level of security is to connect as User_A, calling code in User_B's schema, whicxh references tables in other users' schemas.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
0

A SID is a Service ID and refers to a single Oracle database instance

A service name is very like a SID but multiple SIDs can be referenced by one Service Name

E.g. in a Dataguard scenario, the primary instance might be SID_01 and ORA_SRV. SID_02 and SID_03 are running somewhere on the network and receiving and applying the log files. SID_01 fails and SID_02 is brought up (Mounted and Opened) and now exposes itself ont he network as ORA_SRV.

Similarly with RAC multiple SIDs make up a single Service.

A schema is in effect a user.

A JDBC connection will be in some form of "HOST:PORT:(SID or Service name)". You will have to connect to the DB and will do so with a username and password. The username will (almost always) connect you to a schema of that name. You can can reference objects in another schema with dotted notation in your sql, e.g. SCHEMA.OBJECT_NAME

Karl
  • 3,312
  • 21
  • 27
  • Don't confuse a database and an instance -- a database is the set of files, the instance is the memory areas and processes that access it. RAC has multiple instances per database, each of which can offer some subset of the total services offered by the database. – David Aldridge May 28 '13 at 09:41
  • Yeap, but for someone who is not familiar with Oracle Instance in less confusing that Database. Given that Instance is used in IT to refer to an instance of a system and given that other RDBMSes use Database is subtly different ways keeping it simple is a good idea. – Karl May 28 '13 at 09:47