Today's Question:  What does your personal desk look like?        GIVE A SHOUT

Understanding database, instance and schema in Oracle database

  sonic0002        2016-12-14 23:30:56       38,908        2    

Oracle database is the most popular database system among enterprises. To start working on Oracle database, some concepts must be understood first. They include database, instance, schema and user etc. And among these concepts, some have different meanings from those in other database systems such as MS SQL, MySQL, DB2.

On the web, there are already some good posts which explain different concepts such as Ask Tom's database vs instances, Difference between database vs user vs schema. In this post, these concepts with be summarized based on real application development experience.

Database : A collection of physical operating system files or disk. It refers to a database system on a server. This is different from the database concept in other database systems.  In other database systems, a database belongs to an instance which is a logical database manager environment 

Instance  : An instance is a collection of some process and background process and listeners which is mounted on a database system. An instance can only mount to one database(i.e, belongs to one database). It's different from other database system(DB2 for example) concept where an instance can have many databases under it.  A database can have more than one instance. To switch instance in a database system, one needs to change the value of ORACLE_SID. This concept is similar to the database concept in other database system.

Schema/User : A schema is the set of objects (tables, indexes, views, etc) that belong to a user. In Oracle, a user can be considered the same as a schema. When a user is created, a schema with the same name will also be created. A database instance can have multiple users/schemas. When trying to connect using a user, the command would be sqlplus user/password. In case where a privileged access is needed for accessing different schemas, sqlplus / as sysdba can be used.

Basically in Oracle database, an instance belongs to a database(or further can think it as an instance is a database). In other database systems, a database belongs to an instance and an instance can have multiple databases.

To make it a bit more complicated. Until Oracle 11g version above was valid, except RAC systems where each node contains an instance and manages the database. From 12c it has changed with the multitenancy. 

DIFFERENCE  DATABASE  ORACLE DATABASE  INSTANCE  SCHEMA  USER 

Share on Facebook  Share on Twitter  Share on Weibo  Share on Reddit 

  RELATED


  2 COMMENTS


Anonymous [Reply]@ 2017-11-05 09:48:00
@admin please delete the above comment.. I was just playing around ;)

Nice article BTW :)
Anonymous [Reply]@ 2017-12-04 06:09:31

Thanks, clears my doubts :)