Oracle architecture-Logical Storage architecture

Tag: Data occupies the input file group together with part of the purpose form BSP

1 Overview of the logical structure

Oracle's logical storage structure is a hierarchical structure, which consists mainly of the concepts of table space, segment, interval and data block.

The logical structure is user-oriented, and when the user uses the Oracle design database, the logical storage structure is used;

The Oracle database consists of several table spaces (the database itself is a logical concept);

The table space consists of several segments;

The segment consists of multiple data regions;

The data area consists of multiple data blocks.

2 Block Data Blocks

The data block is the smallest logical unit in the Oracle logical storage structure and the smallest storage unit for the database to perform input and output operations.

Oracle data is placed in the ORALCE data block, not in the operating system block.

Typically, the Oracle chunk size is an integer multiple of the operating system block size. (such as the block size of the OS is 2kb,oracle the chunk size is 8KB).

The Oracle data block has a defined standard size corresponding to the initialization parameter db_block_size. (Oracle supports multiple-size blocks in the same database, divided into standard blocks and non-standard blocks)

viewing standard block sizes

Col name format A30; # format Column Size

Col value format A20; # format Column Size

Select Name,value from v$parameter where name = ' db_block_size ';

Data blocks can hold table data, index data, cluster data, and so on, regardless of what data is stored, its structure is the same.

3 Data area Extent

The data area (also known as the data expansion area) is an Oracle storage structure composed of a contiguous set of Oracle data blocks;

One or more data blocks form a data area, and one or more data regions form a segment;

When all the space in a segment is used, the Oracle system automatically assigns a new data area to the segment;

The data area is used to maintain data of a particular data type, so the data area is the smallest unit of Oracle's allocated storage space.

4 Segment Segment

A segment is a separate logical storage structure used to store space-occupying data objects such as tables, indexes, or clusters, which are a series of data areas allocated for a particular data object;

Whenever a data object with a separate segment is created, Oracle creates a segment for it;

A segment belongs to only one particular data object;

The data area contained in the segment can be discontinuous and can span multiple files;

The purpose of using segments is to maintain specific logical objects.

Oracle databases typically have the following 4 types of segments:

Data segment

Save the data record in the table;

When a table is created, the system automatically creates a data segment named after the table;

Index segment

Save the index;

When an index is created, the system automatically creates an index segment named after the index;

Rollback segment

Saves the rollback entry, which is the old value before the modification;

When a transaction begins processing, the system assigns a rollback segment, and the rollback segment can be dynamically created and revoked;

ORACLE11G system has a default rollback segment, the management mode can be automatic, manual;

Temporary segment

Keep temporary data temporarily;

The Oracle system will not operate the allocation temporary segment in the tablespace dedicated to storing temporary data;

For example, create INDEX, SELECT distinct/group By/order by will be used temporarily in the temporary Tablespace allocation temporary section to save the parsed query statement or the temporary data generated during the sorting process;

If the database often has such statements, it is best to adjust the Sort_area_size initialization parameters to increase the sorting area, so that the sorting operation as far as possible in memory, improve execution efficiency.

5 Table Space

Oracle uses tablespaces to group related logical structures (such as segments, data regions, and so on) together;

Table space is the largest logical partition of a database, and is usually used to store data objects such as data tables, indexes, rollback segments, etc.

Any data object must be specified to be stored in a table space when it is created;

Table space (logical storage structure) corresponds to the data file (physical storage structure);

A table space consists of one or more data files, and a data file belongs to only one table space;

A database has at least one table space (SYSTEM), and the size of the tablespace equals the sum of all the data file sizes that belong to it;

The table space is equivalent to a folder, and the data file corresponds to a file in a folder.

Table Space Usage Specification

Users can create multiple custom tablespaces based on the actual situation of the application system and the type of object they want to store to differentiate user data from system data;

The data of different application systems should be stored in different table spaces;

Files of different tablespaces should be stored on different disks to reduce IO collisions;

Primary table spaces created by default for Oracle 11g

SYSTEM

System tablespace for storing data from internal tables and data dictionaries in Oracle systems.

Oracle itself does not recommend placing logical objects such as user-created tables and indexes in the system table space;

View information about the internal system headers-V$fixed_view_definition

Col view_name format A30;

Col view_definition format A30;

SELECT * from V$fixed_view_definition;

View data dictionary information-Dict

COL table_name format A30;

Col comments Format A30

SELECT * from Dict;

Sysaux

Oracle 11g new default tablespace, which mainly stores data objects other than the data dictionary;

acts as a secondary table space for the system, reducing the load on the system table space;

Typically does not store user data and is automatically maintained internally by Oracle Systems

UNDO

Undo table space for storing revocation information;

When modifying the data table, the Oracle system automatically uses the Undo table space to temporarily hold the old data before the modification, and, after submission, determines when to release some space for the undo table space, based on the length of the system's retention time;

Typically, after an Oracle instance is created, the Oracle system automatically creates a "UNDOTBS1" undo tablespace, and the corresponding data file is "UNDOTBS01." DBF "

USERS

User tablespace, the table space that Oracle advises users to use;

Oracle architecture-Logical Storage architecture