What is schema in oracle

In Oracle, schema is a collection of database objects; an Oracle user corresponds to a schema, and a schema can only be created by creating a user. The schema can be called an alias of user, that is, the schema name is the same as user. The names correspond and are the same.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is schema in oracle

Let’s take a look at their definitions first:

A schema is a collection of database objects (used by a user.) .

Schema objects are the logical structures that directly refer 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 database administrators manage database security.

We can see from the definition that schema is a collection of objects. In order to distinguish each collection, we need to give this collection a name. These names are what we You can see many nodes similar to user names under the Enterprise Manager solution. These nodes similar to user names are actually a schema. The schema contains various objects such as tables, views, sequences, stored procedures, synonyms, indexes, clusters. , and database links.

A user generally corresponds to a schema. The user's schema name is equal to the user name and serves as the user's default schema. This is why we see that the schema names are all database user names under the Enterprise Manager scheme. A new schema cannot be created in the Oracle database. To create a schema, it can only be solved by creating a user (although there is a create schema statement in Oracle, it is not used to create a schema). When creating a user At the same time, a schem with the same name as the user name is created for this user and used as the user's default shcema. That is, the number of schemas is the same as the number of users, and the schema name corresponds to the user name one-to-one and is the same, so we can call the schema an alias of the user. Although this is not accurate, it is easier to understand.

A user has a default schema, and its schema name is equal to 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 through select * from emp; In fact, the complete way of writing this SQL statement is select * from scott.emp. The full name of an object in the database is schema.object, not user.object. Similar to if we do not specify the schema of the object when creating an object, the schema of the object is the user's default schema. This is like a user having a default table space, but the user can also use other table spaces. If we do not specify a table space when creating an object, the object will be stored in the default table space. If we want the object to be stored In other table spaces, we need to specify the table space of the object when creating the object.

Ahem, having said so much, let me give you an example, otherwise, everything will be boring!

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; --改变用户缺省schema名
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 me to use schema as the alias of user. In fact, in terms of use, shcema is exactly the same as user. There is no difference. The user name can also appear where the schema name appears.

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of What is schema in oracle. For more information, please follow other related articles on the PHP Chinese website!