Clarify the relationships between databases, users, solutions, tablespaces, and table objects in Oracle

Original http://blog.csdn.net/nkliming/article/details/7613189
1. The first is the overall structure of oracle.
A database in Oracle is an instance.
One Oracle user is a schema (solution ).
Oracle structure =
Instance> User> table (the user belongs to the database instance and the table belongs to a user)

Therefore, create a tablespace under ORACLE, create a user, set the user's default tablespace, and create a table under the user;
 

  --  Create a data table space  Create  Tablespace ciciloggingdatafile  '  D: \ oraclexe \ app \ oracle \ oradata \ Cici. DBF  '  Size 32 mautoextend  On  Next 32 m maxsize 2048 mextent management local;  --  Create a user and specify a tablespace  Create   User Cici identified By  Ciciprofile  Default  Default  Tablespace Cici account unlock;  --  Grant permissions to users  Grant Connect, Resource To  Cici; Grant   Create Session To  Cici;  --  Login User SQL >  Conn, enter the user name, Cici, and password xxxxxx.  --  -Create a table  Create   Table A (name Varchar2 ( 100 ) Primary  Key  );  --  Query a table  Select   *   From Cici.;

 2. Now let's look at the relationship between the tablespace and the solution.

You can have multiple users under an instance. Each user can have only one schema, but there is no relation between the tablespace and schema.

Condition for schema generation: After a user is created in the database, the user is granted the permission to create tables or other objects. In this case, no shcema exists.Only when this user uses these permissions to create his/her first objectOracle creates a schema for this user to accommodate this object and subsequent objects.

 
After the database is created, Oracle is actually a DBF file, and then n DBF Files form a tablespace:
A database is called Cici,
The user uses three tablespaces in the Cici: u1, U2, and U3.
Where
U1 consists of D: \ 1. DBF
U2 is composed of D: \ 11.dbf D: \ 22. DBF
U3 consists of D: \ 33. DBF
 

Objects of the same schema can be stored in different tablespaces.

Similarly, a tablespace can store objects of different schemas.
Schema is a user and all objects under it, while the tablespace is logically used to put objects, physically corresponding to the data files on the disk or bare devices. 3. Let's look at the relationship between the solution and users.
 The solution is a collection of database objects, and the solution name is the user name.
Schema contains various objects such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.

A new schema cannot be created in the Oracle database,
To create a schema, you can only create one user (the create schema statement in Oracle is not used to create a schema ). The number of Schema users is the same,

Under authorization, one user can use another schema, and one user has only one default schema.
If we access a table without specifying which schema the table belongs to, the system will automatically add the default sheman name to the table.
For example, when we access the database, we can access the EMP table under the Scott user through select * from EMP; in fact, the complete Syntax of this SQL statement is select * from Scott. EMP.
We need to specify the tablespace of the object when creating the object. Schema in Oracle refers to a set of all objects under a user. schema itself cannot be understood as an object. Oracle does not provide the schema creation syntax, the schema is not created when a user is created, but is created after the first object is created under the user. As long as an object exists under the user, the schema must exist, the schema does not exist if no object exists under the user. This is similar to the temp tablespace group. You can also observe it through OEM. If you create a new user, if there is no object under this user, the schema does not exist. If an object is created, a schema with the same name as the user will also be generated. The following are some examples of SQL> gruant DBA to scottsql> Create Table Test (name char (10); table created. SQL> Create Table System. test (name char (10); table created. SQL> insert into test values ('Scott '); 1 row created. SQL> insert into system. test values ('system'); 1 row created. SQL> commit; Commit complete. SQL> conn system/managerconnected. SQL> select * from test; Name ---------- systemsql> alter session set current_schema = Scott; -- change the default schema name session altered. SQL> select * from test; Name ---------- scottsql> select owner, table_name from dba_tables where table_name = upper ('test'); Owner table_name tables ---------------------------- Scott testsystem TestFinally, let's summarize: A database is a big circle with a tablespace in it. A tablespace contains data files. A schema is a logical concept and a set, but a schema is not an object,
Oracle does not provide the schema creation syntax. A tablespace is also a logical concept. It is essentially a collection of one or more data files.
Data files are physical files that store data.
A data file can only belong to one tablespace. A tablespace can contain one or more data files. A database consists of multiple tablespaces, but a tablespace can only belong to one database. The following is an image metaphor taken from the Internet. Let's take a look: We can regard the database as a big warehouse. The Warehouse is divided into many rooms. The schema is the room, a schema represents a room, and the table can be seen as the bed in each schema,
Table is placed in every room and cannot be placed outside the room. Isn't it possible to go to bed homeless at night,
Then many items can be placed on the bed, just like many columns and rows can be placed on the table,
The basic unit for storing data in the database is table. In the display, the basic unit for storing items in each warehouse is the bed, and the user is the master of each schema,
(The schema contains objects rather than users.) The user and schema correspond one by one. Each user can only use its own schema without a special designation,
If a user wants to use something in another schema, it depends on which schema user has given you this permission,
Or check whether the boss (DBA) of the warehouse has granted you this permission. In other words, if you are the owner of a warehouse, the right to use the warehouse and everything in the warehouse are yours,
You have full operation permissions. You can discard unnecessary things from every room or prevent some useful things from going to a room. You can also assign specific permissions to each user, that is, what can he do in a room,
Whether it can only be read-only or have all control rights (R/W) like the master, it depends on the role corresponding to the user.