The relationship between databases, users, solutions, tablespaces, and table objects in Oracle.

1. First, let's take a look at the differences between Oracle and sqlserver.

A database in Oracle is an instance, while in SQL Server, multiple databases can be created under an instance. From the user perspective, one Oracle user is a schema (solution, although not accurate, but easy to understand, I will explain this problem in detail later ), all Tables belong to different users. authorization is required for one user to access the tables of another user. In sqlserver, tables are created in the database, it does not belong to a user. The Oracle structure is ==== instance-> User-> table. The table belongs to a specific user (but the schema is used to index the table during access), so in Oracle
Create a tablespace, create a user, set the user's default tablespace, and create a table under the user; and under sqlserver, the structure is === instance-> database-> table, the user is independent from the database and table. is used to create a database, create tables under the database, create users, and set the user's access permissions to the database. "It can also be understood that there is a database in Oracle, but unlike sqlserver, an oracle instance has only one database, and the basic information of the database system is also stored in this database, unlike SQL Server, which is stored in a separate master database ".

 

2. Now let's look at the relationship between the tablespace and the solution. In fact, the relationship between them is unrelated. There can be multiple users under an instance, and each user can have only one schema. Many people have been thinking that since schema is used to store table objects and tablespaces are also used to store table objects, is there a certain relationship between them, but the result is irrelevant. After the database creates a user and grants the user the permission to create tables or other objects, the mode does not exist. Only when a user uses these permissions to create his/her first object, Oracle creates a schema for the user to accommodate this object and the objects to be created later. The objects of the same schema can be stored in different tablespaces. Similarly, tablespace can also 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. From the definition, we can see that the solution is a set of database objects. To distinguish each set, we need to name this set, these names are also the nodes with similar user names that we see in the Enterprise Manager Solution. These nodes with similar user names are actually a schema, which contains various objects such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links. A user generally corresponds to a schema. The schema name of the user is the same as the user name and serves as the default schema of the user, this is why the schema names we see in the Enterprise Manager solution are database usernames. You cannot create a new schema in the Oracle database. To create a schema, you can only create one user (create
The schema statement is not used to create a schema ). The number of Schema users is the same, and the schema name and user name are the same. Therefore, schema is called the alias of user, which is inaccurate but easy to understand. 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 access the EMP table under Scott and use select * from EMP. In fact, the complete 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 the schema of the object when creating the object, the schema of the object is the default schema of the user. This is like a user has a default tablespace, but this user can also use other tablespaces. If we do not specify a tablespace when creating an object, the object is stored in the default tablespace, to store objects in other tablespaces, 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 temp.
Tablespace group. You can also observe it through OEM. If you create a new user, the schema does not exist if the user has no objects, if an object is created, a schema with the same name as the user is also generated. Below are some examples

SQL> gruant DBA to Scott

SQL> 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/Manager

Connected.

SQL> select * from test;

Name

----------

System

SQL> alter session set current_schema = Scott; -- change the default schema name.

Session altered.

SQL> select * from test;

Name

----------

Scott

SQL> select owner, table_name from dba_tables where table_name = upper ('test ');

Owner table_name

------------------------------------------------------------

Scott Test

System Test

 

Finally, let's make a conclusion: the database is a big circle, with the tablespace in it, the data file in the tablespace, And the schema as a logical concept and a set, however, schema is not an object, and 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-like analogy, which is taken from the Internet. We can think of database as a big warehouse, which has divided many rooms, schema is the room. A schema represents a room. A table can be seen as a bed in each schema. A table is placed in every room and cannot be placed outside the room, isn't it because I went to bed homeless at night, and I could put a lot of items on the bed, just like many columns and rows on the table. The basic unit of data storage 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 owner of each Schema (so the schema contains objects rather than users). The user and schema correspond to each other one by one, each user can only use its own schema. 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 right and can discard unnecessary things from every room, it can also prevent some useful things from going to a room. You can also assign specific permissions to each user, that is, what he can 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.