Data files and log files are the most important files in the database. They are where the data is stored. Each database has at least one data file associated with it, and usually more than one, there are many. How is the data organized in the data file? To understand this, we first have to understand what table spaces (tablespace), segments (segment), zones (extent), blocks, are the basic units in which the Oracle database organizes data in a data file. Now let's understand these concepts.
Blocks are the physical units of data storage and are the most basic units in a data file, and data is stored directly on the block. is the smallest unit of Oracle space allocation. There are three common block sizes in Oracle, 2KB, 4KB, and 8KB. The size of the block is fixed when the database is created, the size of each block in the database is the same, and all the blocks are in the same format, consisting of the "size + Table Directory + row directory + Free Space + data space". The header contains information about the block type (for example, the block, or the index block), the location of the block on the disk, and so on. The table directory, if any, contains information about the tables in this block that store the rows (if data in more than one table exists in a block). Row directory contains a description of the data row, which is an array of pointers that indicate the physical location of each row in the data block. The size, table directory, and row directory are collectively called Block overhead, which is Oracle's original statistics and management block itself. The rest of the two parts are very simple, already have data is the data space, temporarily does not exist is the free space.
Area is called the disk area, is a continuous allocation space in the data file, it is larger than the block, composed of blocks. Some objects may require at least two extents when allocating space, such as a rollback segment, and the two extents do not necessarily require a connection. The size of the area varies from one block to 2GB
Segments are allocation units in an Oracle database, and objects such as tables, indexes, and so on are allocated in segments. When a table is created, a table segment is created, and an index segment is created when an index is created. Each object that consumes storage space is ultimately stored in a single segment. There are rollback segments, temporary segments, clustered segments, index segments, and so on.
A tablespace is a logical container that is associated with a data file, with at least one data file associated with a table space. A table space can have more than one segment and a segment can only belong to a single table space.
Schema, also known as pattern, is a logical concept that is smaller than the table space, and it is also a logical container. Multiple users may share a table space, how do you partition each user? There is a corresponding scenario for each user in the tablespace that holds the information for a single user.
The hierarchical structure stored in Oracle is summarized as follows:
A database consists of one or more table spaces
Table space consists of one or more data files, a table space contains multiple segments
The paragraph consists of one or more districts
The zone is a contiguous allocation space in the data file, consisting of one or more blocks
Five, block is the smallest and most basic unit in the database, is the smallest I/O unit used by the database
Six, each user has a corresponding scheme
1. Service name (actually: database name), a name that must be specified when you install ORACLE
2. tablespace table space, disk storage location for database objects
3. Schema scheme, logical classification of database objects
4. User users, equivalent to schema
5. Service Name > Tablespace > Schema (user)
Detailed Description:
Schema is a collection of database objects, in order to distinguish between the collection, we need to give this collection a name, these are the many similar user names that we see in the Enterprise Manager schema of the node, these are similar to the user name of the node is actually a Schema,schema It 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 is the default schema for that user. This is why we see the schema name as the database username under the schema of Enterprise Manager. The Oracle database cannot create a new schema, and to create a schema, it can only be solved by creating a user method (although Oracle has the CREATE SCHEMA statement, but it is not used to create a schema), creating a user , create a schem with the same name as the user name for this user and act as the default shcema for that person. 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 equals the username and, of course, one user can use a different schema. 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 default schema for user. This is like a user with a default tablespace, but the user can also use other tablespace, if we do not specify tablespace when creating the object, the object is stored in the default tablespace, in order for the object to be stored in the other Tablespace, we need to specify the tablespace of the object when the object is created.
Examples are 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 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 based on the schema as the user alias. 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.
Schema and user are generally consistent, the establishment of a user can be a schema, such as: HR users after the establishment of an HR program, the next to build tables, indexes and other database objects, to specify which schema they belong to, also specify which tablespace to store in.
It can also be understood that schema is the logical attribution and classification of database objects, while tablespace is the physical and actual location of database objects.
A Schema is a collection of objects that the user belongs to, including tables, indexes, views, java,pl/sql blocks, and so on.
The name of the schema is the same as the user name, and when a user is created, the user's corresponding Shema is created simultaneously, and the user name interacts with the schema.
There is no connection between schema and Tablespace, objects of the same schema can be stored in different table spaces, and objects of different schemas can be stored in the same table space.
Do not create additional data objects under the SYS and system schemas.
Schema is the collection of the database objects owned by a database user.
The Schema has the same name as the user owns it.
Schema objects include structures such as tables,views,indexes,java,pl/sql etc..
Schema is no relationship with tablespace.
Object in the same schema can is in different tablespaces.
When a database user was created,acorresponding schema with the same name was created for that user.
Username and Schema are offen used interchangeably.
