Schema Understanding in Oracle

In Oracle, a user is a schema, tables are built in the schema, or they can be understood to have different tables for each user. When a user wants to access another user, that is, a table of another schema,
can be accessed in the form of username.tablename, without the need for distributed transactions at all. Distributed transactions are not for you to do this.

A schema cannot be created in the Oracle database in order to create a schema that can only be resolved by creating a user's method.

Although there is a CREATE SCHEMA statement in Oracle, it is not used to create a schema, as described in the official documentation below.

Use the CREATE SCHEMA statement to create multiple tables and views and perform multiple grants in your own schema in A single transaction.
To execute a CREATE SCHEMA statement, Oracle Database executes included statement. If all statements execute successfully and then the database commits the transaction. If any statement results in a error, then the database rolls back all the statements.

Here to correct some of the misconceptions on the Web, Oracle creates a user and creates a Schem for the user with the same name as the user name and as the default shcema for that user. That is, the number of schemas is the same as the number of user, and the schema name corresponds to the user name one by one and the same. Official documents are as follows

Deep understanding of the differences between user and schema
There is no direct relationship between the different schemas, the tables between the different Shcema can have the same name, or they can be referenced to each other (but must have permissions), and each user can only manipulate all the tables under its own schema without manipulating the schema's root authority. Tables with the same name under different schemas can be stored in different data (that is, the schema user's own data). Like a house filled with furniture, the owner of the House (user), not the house (schema), has the power to dominate the home.
You can also be the owner of a house (user), owning your own house (schema). Can be done by alter session
Into someone else's house. At this time, you can see the furniture in someone else's house (DESC). If you don't specify it, all you do is focus on what's in your current house. As to whether you have permission to use (select), Move (update), or remove (delete) The furniture, look at the house.
Does the owner give you such permission, or you are the Boss (DBA) of the entire building (DB).
If you want to invoke other schema objects (with permission), but do not build synonym, and do not want to put other schema names in the code, you can first use

Alter session set current_schema=<schema_name>.

Practical cases

Sql> SHOW USER;
USER is "SYS"
Sql> alter session set Current_schema=scott;
Session altered.
Sql> SHOW USER;
USER is "SYS" #切换Schema并不等同于切换User
Sql> SELECT COUNT (*) from EMP;
COUNT (*)
----------
14
Sql> alter session set Current_schema=sys;
Session altered.
Sql> SELECT COUNT (*) from EMP;
SELECT COUNT (*) from EMP
*
ERROR at line 1:
Ora-00942:table or view does not exist

Sql> CREATE USER oe111 identified by OEPSWRD;
User created.
sql> CREATE SCHEMA AUTHORIZATION oe111
CREATE TABLE new_product
(Color VARCHAR2 (PRIMARY KEY, quantity number)
CREATE VIEW New_product_view
As SELECT color, quantity from new_product WHERE color = ' RED '
GRANT Select on New_product_view to Scott
/
*
ERROR at line 1:
Ora-02421:missing or invalid schema authorization identifier
Sql> Grant DBA to oe111;
Grant succeeded.
sql> CREATE SCHEMA AUTHORIZATION oe111
2 CREATE TABLE new_product
(Color VARCHAR2 (PRIMARY KEY, quantity number)
CREATE VIEW New_product_view
5 as SELECT color, quantity from new_product WHERE color = ' RED '
6 GRANT Select on New_product_view to Scott
7;
CREATE SCHEMA AUTHORIZATION oe111
*
ERROR at line 1: #切换该语句必须在同一Schema下才能执行

Ora-02421:missing or invalid schema authorization identifier
Toggle Schema

Sql> CONN OE111/OEPSWRD
Connected.
sql> CREATE SCHEMA AUTHORIZATION oe111
CREATE TABLE new_product
(Color VARCHAR2 (PRIMARY KEY, quantity number)
CREATE VIEW New_product_view
As SELECT color, quantity from new_product WHERE color = ' RED '
GRANT Select on New_product_view to Scott
/

Reference to: http://blog.csdn.net/kimsoft/article/details/4627520

Http://www.2cto.com/database/201205/131238.html

Http://blog.sina.com.cn/s/blog_6c845fae0100peof.html

Http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6014.htm

This article is original, reprint please famous source, author

If there is any mistake, please correct me.

Email: [Email protected]

Schema Understanding in Oracle