The Difference between User and Schema in Oracle

As an Oracle developer, you have probably wondered at some point: do user and schema in Oracle really mean the same thing? And if they do, why does Oracle have two names for it?

This post helps to clarify the sameness – or otherwise – of Oracle user and schema.

In the simplest terms,

an Oracle user is a database  account with login access to the database; 
an Oracle schema is an Oracle user plus the collection of database objects owned by the user.

Here is Tom Kyte’s take on the subject: Asktom: What is the meaning of schema?.

Though user and schema differ in their definitions, for most practical purposes the words can be used interchangeably. When you create a user using the CREATE USER command, Oracle automatically creates a schema owned by that user. The schema name is the same as the user name. So the schema owned by user SCOTT is also SCOTT.

But…

USER and SCHEMA are not always identical

When you login to a database using a particular Oracle user, you connect by default to that user’s schema. However, in Oracle it is possible to be connected to the database via one Oracle user and switch to another Oracle user’s schema.

The alter session set current_schema command in Oracle can be used to switch to another schema while being connected to a particular Oracle user. Take a look at the article User != Schema for a lucid illustration of how this can be achieved.

CREATE USER vs CREATE SCHEMA

Let’s revisit a statement made earlier in this post:

When you create a user using the CREATE USER command, Oracle automatically creates a schema owned by that user.

This begs the question: if CREATE USER creates a schema, what then does CREATE SCHEMA do?

Given that CREATE TABLE creates a table, CREATE INDEX creates an index, and so on, one might assume that CREATE SCHEMA would create a schema.

Not so.

CREATE SCHEMA does not actually create a schema. It only lets you create multiple tables/views and grant privileges on them in your own schema, in a single transaction.

Here’s an example of using CREATE SCHEMA in the schema “hr” to create a table, create a view on that table, and give grant to another user on that view.

1 2 3 4 5 6 7 8 9 10 11

SQL> CREATE SCHEMA AUTHORIZATION hr   2  -- must be the same as the current schema name   3     CREATE TABLE test1   4        (col1 VARCHAR2(10) PRIMARY KEY   5       , col2 NUMBER)   6     CREATE VIEW test2   7        AS SELECT col1   8           FROM test1 WHERE col2 = 3   9     GRANT select ON test2 TO dip;   Schema created.

In Conclusion

• Oracle schema = Oracle user + database objects owned by that user.
• When an Oracle user is created using the CREATE USER command, a schema gets created for the user by default.
• There is a one-to-one correspondence between Oracle user name and Oracle schema name.
• While user = schema in most circumstances, that isn’t true all the time.