Concepts of scenarios in Oracle and the relationship between scenarios and databases __ Database

Source: Internet
Author: User

Reprint Address: http://hi.baidu.com/ayongs/blog/item/f93f93519a7ac32143a75be1.html

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

Let's take a look at their definition:
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 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 this userThat 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.
Cough, say so much, give everybody an example, otherwise, all dull.
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; --Changing the user 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 query above is the basis for my saying that the schema is used as the alias for user. In fact, in use, Shcema is exactly the same as user, and the user name can appear where the schema name appears. "The above is transferred from http://bbs.loveunix.net/viewthread.php?tid=13418"

Through the above explanation, it should be easy to understand what is the scheme, I will analyze the relationship between the program and the database.

As mentioned above , the scheme is a collection of database objects used by users, and a user has a default schema whose schema name equals the user name, and of course a user can use other schemas.

We can only log into a certain database in a certain capacity, and then operate in the database, but after a user logs on to a database, you can manipulate the database objects in each of the different scenarios in that database. That is, a database is made up of database objects in a variety of different scenarios, and a scenario is made up of database objects of the schema in different databases.

For example: When we install the Oracle database, the system defaults to generate a database named ORCL, after the installation, we create a database called Fatest, this time, we are the identity of the SYS login ORCL database, we found the system default user (including Sys/system /sysmen and so on) have their own corresponding program. At this point we create a user: TESTUSER, the system will automatically generate a scheme name with the same name for TESTUSER. We can then create a table AA and belong to the scenario testuser, or you can create a table AA with the same name, but it belongs to the Sys. Similarly, we can log in as a testuser user ORCL database objects that manipulate other scenarios of the database.

But we can't manipulate the database objects in Fatest. Then login fatest CREATE TABLE testuser.bb, TESTUSER. AA, then the schema-TestUser database object includes the table AA, BB, and other default objects in the Fatest database, the ORCL table AA, and other default objects.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.