Difference between a user and a schema in Oracle?

Solution 1:

From Ask Tom

You should consider a schema to be the user account and collection of all objects therein as a schema for all intents and purposes.

SCOTT is a schema that includes the EMP, DEPT and BONUS tables with various grants, and other stuff.

SYS is a schema that includes tons of tables, views, grants, etc etc etc.

SYSTEM is a schema.....

Technically -- A schema is the set of metadata (data dictionary) used by the database, typically generated using DDL. A schema defines attributes of the database, such as tables, columns, and properties. A database schema is a description of the data in a database.

Solution 2:

I believe the problem is that Oracle uses the term schema slightly differently from what it generally means.

1. Oracle's schema (as explained in Nebakanezer's answer): basically the set of all tables and other objects owned by a user account, so roughly equivalent to a user account
2. Schema in general: The set of all tables, sprocs etc. that make up the database for a given system / application (as in "Developers should discuss with the DBAs about the schema for our new application.")

Schema in sense 2. is similar, but not the same as schema in sense 1. E.g. for an application that uses several DB accounts, a schema in sense 2 might consist of several Oracle schemas :-).

Plus schema can also mean a bunch of other, fairly unrelated things in other contexts (e.g. in mathematics).

Oracle should just have used a term like "userarea" or "accountobjects", instead of overloadin "schema"...

Solution 3:

From WikiAnswers:

• A schema is collection of database objects, including logical structures such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.
• A user owns a schema.
• A user and a schema have the same name.
• The CREATE USER command creates a user. It also automatically creates a schema for that user.
• The CREATE SCHEMA command does not create a "schema" as it implies, it just allows you to create multiple tables and views and perform multiple grants in your own schema in a single transaction.
• For all intents and purposes you can consider a user to be a schema and a schema to be a user.

Furthermore, a user can access objects in schemas other than their own, if they have permission to do so.