Since an Oracle developer, you control probably wondered next to a number of place: Figure out user and schema in Oracle really mean the same phenomenon? And if they figure out, why does Oracle control two names in support of it?
In the simple way,
- 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.
What is the meaning of schema?
Though user and schema vary in their definitions, in support of the majority convenient purposes the language can be used interchangeably. When you create a user using the CREATE USER demand, Oracle robotically creates a schema owned by with the purpose of user. The schema chooses is the same as the user choose. So the schema owned by user SCOTT is also SCOTT.
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:
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.
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.
SQL> CREATE SCHEMA AUTHORIZATION hr
— must be the same as the current schema name
CREATE TABLE test1
(col1 VARCHAR2 (10) PRIMARY KEY
, col2 NUMBER)
CREATE VIEW test2
AS SELECT col1
FROM test1 WHERE col2 = 3
GRANT select ON test2 TO dip;
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.