What does Oracle mean in the schema?

Source: Internet
Author: User

It seems that some people still in the schema do not understand the real meaning, today, I re-organize, I hope to help.

Let's start by looking at their definition:
A schema is a collection of the database objects (used by a user.).
Schema Objects is the logical structures that directly refer to the database ' s data.
A user is a name, defined in the database, can connect to and Access objects.
Schemas and users Help database administrators manage database security.

From the definition we can see that the schema is a collection of database objects, in order to distinguish the various collections, we need to give this collection a name, these are the many similar username that we see under the Enterprise Manager scheme, These similar username nodes are in fact a schema. The schema contains various objects such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.



A user generally corresponding to a schema, the user's schema name equals username, and as the user's default schema. This is why we see schema names as database username under the Enterprise Manager scenario.

A schema cannot be created in the Oracle database in order to create a schema that can only be solved by creating a user's method (although there is a CREATE SCHEMA statement in Oracle, it is not used to create a schema). At the same time creating a user, create a schem with the same name as username for the user and act as the default shcema for that user.

That is, the number of schemas is the same as the number of user, and the schema name is the same as the user name one by one and the same, all we can call the schema is the user alias. Although this is not accurate, but easier to understand some.

A user has a default schema whose schema name is equal to username. Of course, a user can also use other schemas. Assuming we visit a table without indicating which schema the table belongs to, the system will voluntarily give us the default Sheman name on the table. For example, when we visit the database, we visit the EMP table under the Scott user, through the select * from EMP; In fact, the full wording of this SQL statement is select * from Scott.emp. The full name of an object in the database is Schema.object, not user.object. The similarity assumes that we do not specify the schema of the object when creating the object. The schema for the object is the default schema for the user. This is like a user has a default tablespace, but the user is also able to use other table spaces, assuming that we do not specify a tablespace when creating an object, the object is stored in the default tablespace, and in order for the object to be stored in another table space, we need to specify the table space for the object when it is created.

Cough, say so much, give you a sample. Otherwise, everything is 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; --Change 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
--That's what I'm talking about. The query schema is a user alias. Actual use, Shcema and user completely different, no difference, in the case of the schema place name can appear the user name.

What does Oracle mean in the schema?

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.