The difference between Oracle user and schema __oracle

the difference between Oracle user and schema (original) Blog Categories:Oracle Day-to-day Management

Overview

The schema and user definitions are as follows

A schema is a collection of the database objects (used by a user.).
Schema objects are the logical structures that directly to the database ' s data.
A user is a name defined in the database that can connect to and Access objects.
Schemas and users help the database administrators manage database security.

From the definition we can see that the schema is a collection of database objects, in order to distinguish the collection, we need to give this collection a name, which is our Enterprise Manager in the scheme of many similar user-name nodes, these similar user name node is actually a Schema, The schema contains a variety of objects such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.
A user corresponds to a schema that has a schema name equal to the user name and acts as the default schema for that user. This is the reason why we see schema names as database user names under the Enterprise Manager scheme. A schema cannot be created in an Oracle database, and a schema can only be resolved by creating a single user.

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

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

To correct the erroneous view of the Internet, Oracle creates a user with a schem that has the same name as the user name and is 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 is the same. The official documents are as follows
Note:this statement does not actually create a schema. Oracle Database automatically creates a schema when you create a user ("Create user").

If we visit a table without specifying which schema the table belongs to, the system automatically adds the default Sheman name to the table. For example, when we visit the database, we visit the EMP table under Scott's user and pass

SELECT * from EMP;

In fact, the full syntax of this SQL statement is

SELECT * FROM Scott.emp

The full name of an object in the database is Schema.object, not user.object. Similarly, if we do not specify a schema for the object when we create the object, the schema of the object is the user's default schema.
Deep understanding of the difference between user and schema
There is no direct relationship between the different schemas, the tables between different Shcema can have the same name, or they can refer to each other (but must have permission), and each user can only manipulate all the tables under its own schema without manipulating the root of the other schema. A table with the same name under a different schema can be stored in different data (that is, the schema user's own data). Like a house filled with furniture, it is the owner (user) of the house, not the house (schema), that has dominion over these families.
You can also be the owner (user) of a house and have your own house (schema). Can be done by alter session.
Into someone else's house. At this time, you can see other people's House furniture (DESC). If you do not specifically specify, the actions you do are directed at the things in your current house. As for whether you have permission to use (select), Move (update) or take (delete) These furniture, look at the house.
The owner has given you such permission, or you are the Boss (DBA) of the entire building (DB).
If you want to invoke other schema objects (with permissions), but do not build synonym, and do not want to put other schema names into the code, you can first use the

Alter session set Current_schema=<schema_name>.

actual Case

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 original, reprint please famous source, author

If there is a mistake, please correct me

Email: czmcj@163.com

===========================================================================

differences between users and scenarios in Oracle Tags: User schema 2011-03-07 09:04 read (1124) Comments (0)

From the definition we can see that the schema is a collection of database objects, in order to distinguish the collection, we need to give this collection a name, which is our Enterprise Manager in the scheme of many similar user-name nodes, these similar user name node is actually a Schema, The schema contains a variety of objects such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.

A user typically corresponds to a schema that has a schema name equal to the user name and serves as the default schema for that user. This is the reason why we see schema names as database user names under the Enterprise Manager scheme. A schema cannot be created in an Oracle database, and to create a schema can only be resolved by creating a single user (although there is a CREATE SCHEMA statement in Oracle, it is not used for creating a schema). Create a user with a schem with the same name as the user's 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 all of the aliases we can call schema the user, although this is not accurate, is easier to understand.

A user has a default schema whose schema name equals the user name, and of course a user can use another schema. If we visit a table without specifying which schema the table belongs to, the system automatically adds the default Sheman name to the table. For example, when we visit the database, we visit the EMP table under Scott's user, through the select * from EMP; In fact, this SQL statement is fully written as SELECT * from Scott.emp. The full name of an object in the database is Schema.object, not user.object. Similarly, if we do not specify a schema for the object when we create the object, the schema of the object is the user's default schema. It's like a user has a default table space, but the user can also use other tablespace, if we do not specify the table space when we create the object, the object is stored in the default tablespace, and to have the object stored in another tablespace, we need to specify the table space for the object when we create the object.

A schema in Oracle is a collection of all objects under a user, and the schema itself cannot be understood as an object, and Oracle does not provide the syntax for creating the schema, nor is the schema created when user is created. Instead, the schema is created after the user creates the first object, so long as there is an object under user, the schema must exist, and the schema does not exist under user if there is no object; This is similar to the temp tablespace group, In addition, it can be observed by OEMs, if a new user is created, the schema does not exist if there are no objects under that user, and if an object is created, then the schema with the same name as the user is generated. =====================================================

the difference between Oracle users and Patterns 2010-10-28 11:36 anonymous Internet font Size: T | T

Oracle Users (user) are connected to databases and accessing database objects, so what is the difference between this and schema? Below will take you to find the answer.

AD:

Oracle users are an important concept in an Oracle database, and here's a look at the differences between Oracle users and schemas to help you learn about Oracle users.

(a) What Oracle is called User:
A user is a name defined in the database that can connect to and Access objects.
Oracle users connect to databases and Access database objects. (the user is used to connect to the database to access the database).

(ii) What is the pattern (schema):
A schema is a collection of the database objects (used by a user.).
Schema objects are the logical structures that directly to the database ' s data.
A pattern is a collection of database objects. The schema object is the logical structure of the database data.
(Separate the database objects into different logical structures using patterns).

(iii) The difference between user and schema (schema):
Schemas and users help the database administrators manage database security.
A user is used to connect to a database object. The schema is used to create the administrative object. The pattern is a one-to-one relationship with the user in Oracle.
(But DB2 is different, DB2 a user can correspond to multiple modes, DB2 user is the system, he must first obtain system users to become database users, that is, database users is the system users, only the pattern is the database similar users. Interested to study. Here is not the topic, this is DB2 special.

From the definition we can see the schema as a collection of database objects, in order to distinguish the collection, we need to give this collection a name, these are the names we see in the Enterprise Manager scheme of many similar user-name nodes, these similar user name nodes is actually a schema, The schema contains a variety of objects such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links. A user typically corresponds to a schema that has a schema name equal to the user name and serves as the default schema for that user. This is the reason why we see schema names as database user names under the Enterprise Manager scheme. A schema cannot be created in an Oracle database, and to create a schema can only be resolved by creating a single user (although there is a CREATE SCHEMA statement in Oracle, it is not used for creating a schema). Create a user with a schem with the same name as the user's 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 all of the aliases we can call schema the user, although this is not accurate, is easier to understand.

A user has a default schema whose schema name equals the user name, and of course a user can use another schema. If we visit a table without specifying which schema the table belongs to, the system automatically adds the default Sheman name to the table. For example, when we visit the database, we visit the EMP table under Scott's user, through the select * from EMP; In fact, this SQL statement is fully written as SELECT * from Scott.emp. The full name of an object in the database is Schema.object, not user.object. Similarly, if we do not specify a schema for the object when we create the object, the schema of the object is the user's default schema. It's like a user has a default table space, but the user can also use other tablespace, if we do not specify the table space when we create the object, the object is stored in the default tablespace, and to have the object stored in another tablespace, we need to specify the table space for the object when we create the object.

"Edit Recommendation"

View Oracle User Rights

Introduction to Oracle Object permissions

Three kinds of Oracle weighting

Teach you how to save pictures in Oracle

Implementation of Oracle Tree query

0
Top 0
Step