ORACLE of the two concepts: user, and the difference between schema and contact

Jan 18 posted in Oracle (RSS), 196 views.

Today, the official ORACLE re-read the document "concepts", read the schema of the basic concepts of the understanding of its mode for even the official documentation on the schema like this explanation:

"A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are the logical structures that directly refer to the database's data. Schema objects include structures like tables, views, and indexes. (There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.) "

Inside a little more to understand the official documents, schema is a collection of data objects, including those such as tables, views, indexes, synonyms, and so can be said to schema objects. But this is not vivid, on-line an essay which the relationship between the schema and the user with a vivid metaphor explained very thorough, reference to the following:

"User that the Oracle of the user, and all the systems concept similar to the user, the user holds plenty of system privileges and resources; the schema is covered by various objects, which contains tables, functions, packages, and so the object's" location "does not include the right to their access control. like a house, filled with furniture, furniture, have control over these is the house owner (user), rather than the house (schema). You can also a master of the house (user), own their own homes (schema). can alter session way into someone else's house. If you have not specified, you do operations are directed at where your current house things. Whether you have permission to use (select), moving (update), or taken away (delete) these pieces of furniture will depend on the owner of this house have not given permission to like you, or you are the whole building (DB) of the boss (DBA). alter session set schema can be used instead of synonyms. If you want to call the other schema objects (with permission of the premise), but did not build synonym, while not trying to put the code names of other schema, you can first use alter session set schema = "Additional schema names". "

This text made it very vividly, the difference between the user and the schema set out very thoroughly, the following specific examples to deepen through the user and schema for understanding the differences between the two:
The first step in order to sys User Login SQL and the establishment of a regular user storm and penguin:
$ Sqlplus / as sysdba

SQL> create user storm identified by storm;

User created.

SQL> create user penguin identified by penguin;

User created.

The second step, to give some basic user permissions to the new storm and the penguin:
SQL> grant connect, create table, resource to storm, penguin;

Grant succeeded.

The third step is to storm the user login, create a table and insert data:
SQL> conn storm / storm
Connected.
SQL> create table t (id int);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> commit;

Commit complete.

The fourth step is to penguin user login, check to see whether the storm inside the user to build the table data:
SQL> conn penguin / penguin
Connected.
SQL> select table_name from user_tables;

no rows selected

SQL> show user;
USER is "PENGUIN"
SQL> select * from storm.t;
select * from storm.t
*
ERROR at line 1:
ORA-00942: table or view does not exist
From the above results can be seen, the user can not view the user penguin built storm inside the contents of the table, or even been told not this form.

The fifth step, to modify the current schema for the storm, and continues to check:
SQL> alter session set current_schema = storm;

Session altered.

SQL> show user;
USER is "PENGUIN"
SQL> select * from storm.t;
select * from storm.t
*
ERROR at line 1:
ORA-00942: table or view does not exist

Still can not see.

The sixth step, in order to storm the user login to give the penguin a user to view t table permissions:
SQL> conn storm / storm
Connected.
SQL> grant select on t to penguin;

Grant succeeded.

Seventh step, in order to penguin user login, view storm the user's t table:
SQL> conn penguin / penguin
Connected.
SQL> select * from storm.t;

ID
----------
1

More simple to change the current schema for the storm, you can simplify the query process:
SQL> alter session set current_schema = storm;

Session altered.

SQL> select * from t;

ID
----------
1

This experiment down to the user and the link between the schema should have a better understanding of the.