Introduction to Oracle's database storage architecture

Introduction to Oracle Database storage structure

We know that a database is a container for storing data, and its primary function is to save and share data. In this article, we introduce the basic knowledge of database storage data.

First, the storage structure of an Oracle database can be divided into a logical storage structure and a physical storage structure, and Oracle is managed separately for both of these storage structures.
Logical storage structure: the way in which data is organized and managed within Oracle.
Physical storage structure: how Oracle External (operating system) organizes and manages data.

Next we have a detailed description of the two structures above.
1. Logical Storage structure:

Oracle logically divides the saved data into small units for storage and maintenance, and a higher level of logical storage structure is made up of these basic small units.
Logical structure types from small to large oita can be divided into: block (blocks)--> (extent)--> (segment)--> table space (tablespace)

Block: The smallest unit that Oracle uses to manage storage and the smallest logical storage structure. The Oracle database is read and write in blocks when it makes input and output. We recommend that the size of the data block is an integer multiple of the size of the operating system block (1, 2, 4, and so on). In addition, the size of the block is determined when the database is created and cannot be modified. Of course, Oracle also supports different table spaces with different block sizes. Below, we introduce some of the structure of the block.
Header information area of the BLOCK:
Block Size: General property information that contains blocks such as the physical address of the block, the type of segment to which the block belongs
Table directory: If the data stored in the block is table data, save information about the tables contained in the block in the table directory
Row directory: Related information for row records such as rowID
Block storage: Mainly contains free space and already used space. Oracle mainly manages this part of the space with the following two parameters
Pctfree parameter: Specifies the minimum amount of free space that must be retained in the block. When the free storage space in the block is reduced to the proportions set by Pctfree, Oracle marks the block as unavailable and the new data row cannot be added to the block.
pctused parameter: Create a percentage that is marked as available when the storage space already used in the block is lowered to this percentage only.
Please note that the above two parameters can be set at the table space level or at the segment level. The set priority for the segment level is higher.

Area: The logical storage structure that is higher than the block is made up of contiguous blocks, the smallest unit of Oracle's allocation and recovery of storage space. When you create a database object with a separate segment structure, such as tables, indexes, and so on, Oracle creates a data segment for the database object and assigns an ' initial area ' to the data segment. The way in which the subsequent extents are allocated varies according to the table's spatial management approach, such as autoextent,uniform size or setting the default storage clause when the table or tablespace is created. In addition, users are able to reclaim unused extents in objects such as tables, indexes, and so on by executing the following command:
Sql>alter table table_name deallocate unused;
Segment: consists of multiple areas that can be contiguous or discontinuous. Oracle creates "segments" for these objects when the user creates objects in the database that have an actual storage structure (objects that hold the data), such as tables, indexes, and so on. A general object has only one segment. When you create a segment, you can specify Pctfree, pctused, and other parameters to control how the block is managed, or you can specify storage parameters such as initial, NEXT, and pctincrease to specify how the area is allocated. If you do not specify these parameters for a segment, the paragraph automatically inherits the corresponding parameters of the table space. Different types of database objects have different types of segments:
Data segments: Saving records in a table
Index segment: Index entries in an index
Temporary segment: When performing operations such as queries, Oracle may need to use temporary storage space to temporarily save parsed query statements and temporary data generated during the sort process.
Rollback segment: Save rollback data.

Tablespace: The most advanced logical storage structure in which a database is made up of multiple table spaces. When you create a database, some default table spaces are created automatically, such as System tablespace, Sysaux tablespace, and so on. By using table space, Oracle combines all the relevant logical structures and objects. We can specify storage parameters at the table space level, or at a short level. Here's a list of common table spaces, and the information they store:
Datasheet space: A plain tablespace used to store user data.
system table Space: The default tablespace, which holds the data dictionary (a set of internal system tables and views that hold the database's own information, and other objects used internally by Oracle), saves all source code and parsing code for all PL/SQL programs, including stored procedures and functions, packages, database triggers, and so on. Saves the definition of a Database object (table, view, sequence).
Rollback tablespace: For rollback segments, each instance can use only one undo Tablespace
Temporary tablespace: Storage of temporary data generated during SQL execution
Finally, we can use the following metaphor to describe the logical structure relationship:
Block: A piece of white Paper---> Area: White Paper---> paragraph: A number of books put into a file bag---> table space: File cabinets (multiple file bags)

2. Oracle Physical storage Structure
An Oracle database is logically composed of one or more tablespaces, each of which is physically composed of one or more data files, and each data file is made up of data blocks. So, logically the data is stored in the tablespace, and physically in the corresponding data file in the table space. We can use this picture to describe the relationship between the data file and the table space.

The physical files that make up the database are mainly three kinds:
Data files: Storing database data.
Control files: Store the basic information of the database, tell the database where to find the data file and redo log files. It is important to have a successful startup and uptime of the database.
Redo log Files: Store changes to the data. In at least two groups, Oracle uses them in a circular fashion.