Sunday, November 25, 2012

What is a Schema in Oracle ?

This is one of the basic question we mostly encounter in RDBMS questions.

What is a Schema ?
A Schema is a collection of certain database objects  such as tables, index , and views, all of which owned by a user account.

You can think of schema as being the same thing as user account, but there is a little difference b/w them.
user account houses the object own by a user and schema is a set of objects housed therein. 
Definition you will find in a oracle documentation - "logical collection of database objects".

Ideally a "schema" should not be a random collection of objects, but the fact that there is nothing built into oracle that prevents user from doing that- randomly collecting objects into a user account.
A user account should be seen and used as logical collection of database objects,driven by business rules,collected into one organized entity- the schema.

A Schema has the same name as the user account. But it entirely possible to create a schema whose owner is not a human being at all, but perhaps a background application.

Schema and Non-Schema Objects ?
All database objects fall into 2 category or types.

  • Schema
  • Non-Schema

"Schema" objects are those objects own by a user account.
"Non-schema" objects cannot owned by user account.
For example, USER is non-schema objects .Think about it, how can a user account own itself.

Schema Objects                   Non-Schema Objects
Tables                               Users
Constraints                        Roles
Indexes                             Public Synonyms 
Views
Sequence
Private Synonyms

1 comment: