Overview of Oracle Database Structure

I. Physical Structure:

1. Data Files: the Oracle database contains several data files, which store database data, including tables and indexes. Several features of data files:

1) only one data file can be allocated to one database.

2) data files can be set to automatically scalable

3) one or more data files form tablespaces

During database operations, the database first looks for the data to be operated from the memory. If no data is found, it then retrieves the data from the data file and stores it in the memory, then perform operations on the data in the memory. The operated data is not immediately written to the data file (which reduces disk I/O), but stored in the memory. Then, the DBWn process determines when to write data files in batches.

2. Control File: each database has one or more control files. The control file contains the physical structure of the database, including:

1) Database Name

2) data file name and location

3) redo log file name and location

4) database creation time and so on

Generally, a database has several control file images. When the database is opened (ALTER

Database open), read the information in the control file to OPEN the DATABASE. When the physical structure of the database changes, such as adding a data file and a group of redo logs, the control file is automatically modified accordingly. After the physical structure of the database changes, it is best to back up the control file again for database recovery.

3. redo log file: redo log records data changes. Generally, a database has two or three groups of redo log files. Images in the same log group are best distributed on different disks.

4. Archiving logs: When the database starts archiving, redo logs are automatically archived to the specified location.

5. initialization parameter file: contains the configuration information during database startup.

6. Warning and tracking log files

1) Tracking file: each background process has a separate tracking file. For example, when the system finds a problem with a process, the relevant information will be written to the corresponding tracking file. You can find and debug database errors from database trace files.

2) Warning file, also known as warning log. It is a special tracking file that records information about database startup and running. It records data in chronological order.

7. Backup Files

Ii. Logical Structure

1. tablespace: a set of logical objects. In Oracle10g, the SYSTEM and SYSAUX tablespaces are automatically created when the database is created.

2. Data Block: data is stored in a data block. The size of a data block (DB_BLOCK_SIZE) is determined by the operating system block. You can specify 5 types: 2 K, 4 K, 8 K, 16 K, and 32 K.

3. Zone: A series of continuous data block components that store specific types of data, such as indexes and tables.

4. segment: it consists of a series of segments.

1) Data Segment: for each non-clustered table, there is a data segment, and all the data of the table is stored in this segment. Each aggregation has a data segment, and the data of each table in the aggregation is stored in this segment. Each partition in a partition table has a data segment, and the data in the partition is stored in this segment.

2) index segment: Each index has an index segment that stores index data. Each partition in a partitioned index has an index segment.

3) rollback segment: used to temporarily store information to be undone. This information is used to generate read consistency database information and roll back uncommitted transactions when the database is restored. System rollback segments are used to process system transactions. We do not recommend that you use system rollback segments for other operations.

4) Temporary segment: When an SQL statement requires a temporary workspace, Oracle creates a temporary segment. After the statement is executed, the temporary segment is returned to the system.