Oracle-based storage structure

1. Storage Structure
The storage structure of Oracle databases is divided into logical and physical storage structures.

Figure 3

2. Logical Structure

The logical storage structure mainly describes the internal storage structure of Oracle databases, that is, the technical concept of how to organize and manage data in Oracle databases.

Figure 4 Logical Structure
Logically, a database consists of one or more tablespaces. A table space is composed of a group of segments. A segment is composed of a group of partitions, an extent is composed of a batch of database blocks, and a database block) corresponds to one or more physical Blocks
A tablespace is the largest logical unit and a block is the smallest logical unit.
Therefore, the logical storage structure is independent of the operating system platform and is created and managed by the Oracle database.

 

3. Physical storage structure
The physical storage structure mainly describes the external storage structure of the Oracle database, that is, how to organize and manage data in the operating system.
Physically, databases are composed of control files, data files, redo log files, parameter files, and other operating system files.
Therefore, the physical storage structure is related to the operating system platform.

 

3.1. Database Blocks

A database block is the smallest I/O unit used by a database, also known as a logical block or an oracle block. A database block corresponds to one or more physical blocks. The block size is determined by the db_block_size parameter.
The block size is an integer multiple of the operating system block size.
Taking Win2k as an example, the size of the operating system block (OS block) is 4 kb, so the size of the Oracle block can be 4 kb, 8 KB, 16 KB, and so on.
If the block size is 4 kb, the data in each row of a table is 100 bytes ., if a query statement returns only one row of data, when the data is read to the data cache, the read data volume is 4 kb instead of 100 bytes.
A data block consists of the following five parts:
Title: contains general block information, such as block address/segment type. The optimal size is-bytes.
Table Directory: stores information about clustering tables. This information is used for clustering segments.
Row Directory: including valid row Information in this section. 2 bytes starting with each row are allowed.
Free Space: A group of spaces that can be inserted or modified.
Row data: Stores Table or index data.

 

3.2. Zone

A zone (extent) is the logical unit for database storage space allocation. A zone consists of a group of database blocks and is allocated by segments. The first allocated zone is called the initial zone, the area to be allocated later is called the incremental area.

 

Section 3

A segment is a logical storage structure of a specified type in a tablespace. It consists of one or more partitions. segments occupy and increase storage space.
Bootstrap segment: defines a data dictionary table.
Temporary segment (temporary segment): stores the data of the temporary table of the resume during the table sorting operation.
Rollback segment: stores the position and value before modification.
Index segment: stores all the index data that is best queried on a table.
Data Segment (Date segment): stores all data in the table

 

3.4. tablespace

A table space is the largest logical unit. It corresponds to one or more data files. The size of a table space is the total size of the corresponding data files.

Figure 6 Relationship Between tablespaces and data blocks
 
Oracle 10 Gb automatically created tablespaces include:
Example (instance tablespace)
Sysaux (Auxiliary System tablespace)
System (system tablespace)
Temp (temporary tablespace)
Undotbs (redo tablespace)
Users (user's tablespace)
System: The system tablespace that stores management information about the tablespace name, control file, and data file. It is the most important tablespace. it belongs to the sys and system Schemas and is only used by these two or other users with sufficient permissions. However, the system tablespace cannot be deleted or renamed.
Temp: Temporary tablespace stores temporary tables and temporary data for sorting.
Users: A User tablespace that permanently stores user objects and private information. It is also used as a data table space.
Sysaux: Auxiliary System tablespace used to reduce system load and improve system operation efficiency.
Generally: system users use system tablespaces, while non-system users use users tablespaces.

 

4. Physical Structure
Physically, databases are composed of control files, data files, redo log files, and other operating system files.

A data file is a file that physically stores data in an Oracle database. Each data file is associated with only one database. Once a data file is created, its size cannot be modified. A tablespace can contain one or more data files. A data file can belong to only one tablespace.

 

Redo log files record all changes to database data for data recovery. Each database contains at least two Log File groups. Log File groups are written cyclically. Each log file member corresponds to a physical file.

The log switch is set to realize the recycling of Log File groups. The log switch is displayed as follows: When a log file group is filled, the database is closed, and the DBA manually transfers the log switch;

Image Log Files are copies of two or more online log files on different disks to prevent log files from being lost.
Each log file group contains at least two log file members. The number of members in each group is the same. All members in the same group are modified at the same time. The size of members in the same group is the same, and the size of members in different groups can be different.

A control file is a small binary file used to describe the database structure. Maps physical database files to logical table spaces in the data dictionary and online redo log files.
The description is as follows:
The date when the database was created.
Database Name.
The name and path of all data files and log files in the database.
The synchronization information required to restore the database.
Important Notes:
You must access this file when you open and access the database.
Image control file.
The record control file name and path parameters are: control_files

The parameter file is a text file that can be directly modified using the text editor in the operating system. The file is accessed only when a database is created or the instance is started. You must disable the instance before modifying the file.
Initial Parameter file: init. ora
Generate the parameter file: initsid. ora
Config. ora
Role of the parameter file:
Determine the size of the storage structure.
Set all default values for the database.
Set the database range.
Set various physical properties of the database.
Optimize database performance.