Are SCHEMA and USER the same thing in Oracle?

Question

Are SCHEMA and USER the same thing in Oracle? Is there is situation where a single user can have two or more schemas?

there is already pretty good answer here :) [enter link description here](http://stackoverflow.com/questions/880230/difference-between-a-user-and-a-schema-in-oracle) — MoneyPot, Apr 28 '17 at 07:20
Possible duplicate of [Difference between a user and a schema in Oracle?](http://stackoverflow.com/questions/880230/difference-between-a-user-and-a-schema-in-oracle) — William Robertson, May 01 '17 at 10:45

APC · Accepted Answer · 2017-04-28T07:32:49.037

Strictly speaking a SCHEMA is the set of objects owned by a USER. However, a USER can have only one SCHEMA, so people often use the terms interchangeably.

Here's the difference between USER and SCHEMA. User "A" has granted rights on its PRODUCT table to user "B". User "B" does not have a table of that name.

SQL> show user
USER is "B"
SQL> select count(*) from products
  2  /
select count(*) from products
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(*) from a.product
  2  /

  COUNT(*)
----------
         2

SQL>

Without a synonym "B" needs to prefix the schema to reference the PRODUCT table. Unless they choose to change the current schema:

SQL>  alter session set current_schema = "A"
  2  /

Session altered.

SQL> select count(*) from product
  2  /

  COUNT(*)
----------
         2

SQL> show user
USER is "B"
SQL>

Now the user is "B" but the current (default) schema is "A". Which menas they need to prefix the schema when referencing their own tables!

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
YOUR_TABLE

SQL> select count(*) from your_table;
select count(*) from your_table
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(*) from b.your_table;

  COUNT(*)
----------
         6

SQL>

"Whether a schema can be created separately Without any user ."

No. A SCHEMA must belong to a USER. When we create user Oracle creates a schema, an empty one, automatically.

Confusingly there is a create schema syntax, but that is just a short hand for creating a number of tables (and indexes) in one statement. Find out more.

Thank you for the response. But i have a further clarification, Whether a schema can be created separately Without any user . And if so, Please let me know the syntax to create a Schema. — JLP, Apr 28 '17 at 07:23
@JLP No, a schema cannot be created separately. If you want you could create a user and lock the account and not grant the `CREATE SESSION` privilege so there is a schema but the user cannot access it; however, there still has to be a user. — MT0, Apr 28 '17 at 07:31

Are SCHEMA and USER the same thing in Oracle?

1 Answers1