Oracle database structure

table spaces and data files

1. Tablespace is a disk logical space for a database.

2. Each table space consists of one or more data files.

3. A data file can only belong to one table space.

After completing the Oracle system installation and creating an Oracle instance, the Oracle system automatically creates multiple tablespaces and data files.

SELECT file_name, Tablespace_name, BYTES from Dba_data_files;

　　

temporary tablespace and temporary files

A temporary tablespace is a special table space that Oracle systems use to store sorting. When sorting is required in an operation, the Oracle system stores the sorted data in the tablespace and frees up the occupied space when the sort is complete.

SELECT file_name, Tablespace_name, BYTES from Dba_temp_files;

　　

Storage Structure

When Oracle creates a data file, it actually quickly re-formats the disk's operating system as an Oracle block, and each data block is uniquely identified.

When a user designs a database structure, it stores tables, indexes, and so on to an existing table space, and tables, indexes, and so on are all objects that occupy space, collectively referred to as segments. Segments are composed of multiple intervals, which are composed of multiple contiguous blocks of data.

Control Files

A control file is an internal binary file that stores information about Oracle instances, data files, and log files.

When Oracle starts normally, it accesses the initialization parameter file SPFile First, then allocates memory for the system global Zone (SGA), and then Oracle automatically reads out all the data files and log files in the control file.

Oracle for security reasons, when installing an Oracle database or creating an instance, multiple control files are automatically created to ensure that one control file is corrupted at runtime and the system can still work with another control file.

　　View the structure of the V_$controlfile view

DESC V_$controlfile;

　　

　　Querying the control file for information

SELECT NAME, STATUS from V_$controlfile;

　　

log File Redo log Files

This log file is used to record all transaction (Transaction) information that has occurred in the database, as well as database change information caused by Oracle internal behavior. The data of the transaction that has occurred can be read from the log file when the database is resumed. During a database run, when the user issues a commit command, the database logs each transaction information in the log file and writes the log file successfully before returning the completion information to the user.

Each Oracle instance uses a log thread to record changes to the database. A log thread consists of multiple log groups, which are composed of multiple log members.

The Oracle instance runtime generates log information that is first recorded in the system global Zone (SGA) log buffer, and when a commit command is issued, the LGWR process reads the log information from the log buffer and writes it to the file with the smallest log file group sequence number, and writes the next log group when a log group is full. When the LGWR process uses all the log files that can be used, it goes back to the first log group to overwrite the write log information again. When LGWR writes a log group to write another log group, which is called log switchover, the log switch logs the appropriate information in the warning log file (alter_sid.log).

Oracle database structure