What is Oracle Schema

Targeted Audience: Beginners

Till today I was of the illusion that USER and SCHEMA are both equal. But Oracle Guru Andrew Clarke describes the difference between both USER and SCHEMA in his blog. Thanks Clarke for your blog.

So understanding this difference we will define what an Oracle Schema is:

A Schema represents set of objects owned by the user of the same name. For example in an organization there are 100 employees, each of these employees should have a separate space where they manage their objects. It is synonymous with the concept of an employee been allocated a new cabin where he can keep or organize his belongings.

The same way in Oracle a user must be created for each database user. An organization can keep its own rules in naming the users but it is better to use a naming notation always in such cases. If a database user logs in to his space (using connect) he can create objects which becomes the schema.

What a schema can contain?
Just like a cabin where the employee sits; it can contain a PC, a deskphone, a cabinet for filing papers etc., a schema can contain various objects like tables, views, indexes etc. If you want to create any object it must be created inside any of available schemas.

How do I access my schema?
Accessing of a schema is guarded by a password which the DBA assigns at first. You can choose to change the password.