In-depth discussion: Concepts of solutions in oracle and the relationship between solutions and databases

First, understand the concept of the solution, and then understand the relationship between the solution and the database.

Let's take a look at their definition:

Copy codeThe Code is as follows: SchemaIs a collection of database objects (used by a user .).
Schema objectsAre the logical structures that directly refer to the database's data.
A UserIs a name defined in the database that can connect to and access objects.
Schemas and usersHelp database administrators manage database security.

From the definition, we can see that schema is a set of database objects. To distinguish each set, we need to name this set, these names are the nodes similar to user names that we see in the Enterprise Manager Solution. These nodes similar to 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 user's default schema. This is why the schema names are database usernames in the Enterprise Manager Solution.You cannot create a new schema in an Oracle database. To create a schema, you can only create one user, but it is not used to create a schema). When creating a user, create a schem with the same name as the user name and use it as the default shcema for the user..That is to say, the number of schemas is the same as the number of users, and the schema names are one-to-one and the same as the names of users. All aliases that can be called schema as users are inaccurate, but it is easier to understand.

A user has a default schema, and Its schema name is the same as the user name. Of course, a user can also use other schemas. 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 the scott user throughSelect * from emp;In fact, the complete SQL statement is writtenSelect * from scott. emp. The complete name of an object in the database isSchema. objectNotUser. object. Similarly, if we do not specifySchemaInSchemaIs the user's defaultSchema. 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.
Ke, I have said so much. I will give you an example. Otherwise, everything will be boring!Copy codeThe Code is as follows: 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

-- The above query is the basis for using schema as the alias of the user. In fact, shcema is exactly the same as the user, and there is no difference. The user name can also appear in the place where the schema name appears. Through the above explanation, it is easy to understand what the solution is. I will analyze the relationship between the solution and the database.

As mentioned above, the solution is a collection of database objects used by users. A user has a default schema, and Its schema name is equivalent to the user name. Of course, a user can also use other schemas.

We can only log on to a database as an identity and perform operations in the database. However, after a user logs on to a database, he can operate on the database objects of different solutions in the database. That is to say, a database is composed of database objects of different solutions, and a solution is composed of database objects of the solutions in different databases.

For example, when we install the ORACLE database, the system generates a database named ORCL by default. After the installation, we create a database named FATEST. At this time, we logged on to the ORCL database as SYS and found that the default users (including SYS/SYSTEM/SYSMEN and so on) have their own solutions. Create another user: TESTUSER. The system will automatically generate a solution name with the same name for TESTUSER. Then we can create a table named AA, which belongs to the TESTUSER solution. We can also create a table named AA but belongs to SYS. Similarly, we can log on to ORCL as a TESTUSER to operate the database objects of other database solutions.

However, we cannot operate database objects in FATEST. Then log on to FATEST to create the table TESTUSER. BB, TESTUSER. then, TESTUSER's database objects in the solution include FATEST database tables AA, BB, Other Default objects, ORCL table AA, and Other Default objects.