Systems Engineering and RDBMS

Instance, Database and Schema

Posted by decipherinfosys on March 8, 2007

Instance, Database and Schema: The distinction between these three is probably one of the most confusing concepts across different RDBMS since different RDBMS have different architectures and different terminologies.  To put it simply in Oracle lingo, a database is a set of files (data files, redo log files, control files and so on).  An instance is a set of processes (SMON, PMON, MMON, DBWR, etc) and a shared memory segment (SGA).  A database may be mounted and opened by many instances (RAC installation) concurrently (this is to do with the shared everything architecture of Oracle RAC).  An instance may mount and open any database — however it may only open a single database at any time.  So, in ERD terms: Database:Instance :: 1:N (a one to many relationship) in Oracle.  A schema is the container of the schema objects so all the tables, views, procedures, packages, views etc. reside in the schema.  There can be multiple schemas per database.

In the case of DB2 and SQL Server, an instance can have many databases which internally can have many schemas.  In the case of Informix, an instance can have many databases and there is no concept of schemas.  For the sake of simplicity, we refer a database as the container for the different schemas and a schema to be the one that contains all the schema objects (tables, views, procedures, packages, sequences etc.).

One Response to “Instance, Database and Schema”

  1. […] by decipherinfosys on February 9, 2009 In response to one of our previous post, a reader asked us to explain the basic Oracle architecture.  There are several books devoted to […]

Sorry, the comment form is closed at this time.

%d bloggers like this: