What does the schema in Oracle refer to?

Source: Internet
Author: User

It seems that some people still do not understand the true meaning of the schema, and now I have to tidy up a bit, I hope to help you.

Let's start by looking at their definition:
ASchemais a collection of the database objects (used by a user.).
Schema objectsIs the logical structures that directly refer to the database ' s data.
AUseris 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 the schema is a databaseThe collection of objects, in order to differentiate the collections, we need to give this collection a name, which is what we see under the Enterprise Manager scheme of many similar user names node, these are similar to the user name of the node is actually a Schema,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, the user's schema name equals the user name, and is used as the default schema for that user. This is why we see the schema name as the database username under the Enterprise Manager scenario. OracleThe database cannot be created in a new schema, to create a schema, can only be solved by creating a user's method (although Oracle has the CREATE SCHEMA statement, but it is not used to create a schema), Create a user with a schem that has the same name as the user name 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 corresponds to the user name one by one and the same, all we can call the schema as the user alias, although this is not accurate, but it is easier to understand some.

A user has a default schema, whose schema name is equal to the user name, and of course a user can use other schemas. If we visit a table and do not indicate which schema the table belongs to, the system automatically adds the default Sheman name to the table. For example, when we access the database, we access the EMP table under the Scott user, through the select * from EMP; In fact, the complete syntax for this SQL statement is select * from Scott.emp. The full name of an object in the database is Schema.object, not user.object. Similar if we do not specify the schema of the object when creating the object, the schema of the object is the user's default schema. This is like a user with a default tablespace, but the user can also use other tablespaces, and if we do not specify a tablespace when we create the 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 everybody an example, otherwise, everything is dull!
[SQL]View Plaincopy print?
  1. Sql> Gruant dba to Scott
  2. sql> Create table Test (name char (10));
  3. Table created.
  4. sql> Create table system.test (name char (10));
  5. Table created.
  6. sql> INSERT INTO test values (' Scott ');
  7. 1 row created.
  8. sql> INSERT INTO system.test values (' system ');
  9. 1 row created.
  10. Sql> commit;
  11. Commit complete.
  12. Sql> Conn System/manager
  13. Connected.
  14. Sql> SELECT * from test;
  15. NAME
  16. ----------
  17. System
  18. sql> ALTER SESSION SET current_schema = Scott; --Change user Default schema name
  19. Session altered.
  20. Sql> SELECT * from test;
  21. NAME
  22. ----------
  23. Scott
  24. Sql> Select owner, table_name from dba_tables where table_name=Upper (' test ');
  25. OWNER table_name
  26. ------------------------------ ------------------------------

--above this query is the basis for me to say that the schema is the alias of the user. In fact, in use, Shcema is exactly the same as the user, there is no difference, the user name can appear where the schema name appears.  

(turn) What does the schema in Oracle refer to?

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.