Basic Introduction to Oracle tablespace and oracle table

Basic Introduction to Oracle tablespace and oracle table

I. tablespace Basics

Tablespace is a logical structure used in Oracle to manage database data storage. The tables and indexes of each application are stored as one segment, and these segments are stored in the data file as the tablespace component. A tablespace is composed of one or more data files.

The Data Storage Structure in the tablespace can be subdivided into segments, partitions, and data blocks:

Data block: The storage base of the Oracle database, consisting of bytes on the disk. The initialization parameter DB_BLOCK_SIZE specifies the size of the data block, generally between 2 and ~ Between 32 KB. The default value is 8 KB.

Zone (extent): it consists of a specific number of adjacent data blocks and is the unit for space allocation in Oracle.

Segment: A group of segments used to store various logical objects (such as tables, indexes, or other objects). The data of each table is stored in its own data segment, indexes are stored in your own index segments.

The simplest Oracle database must have System and Sysaux tablespaces. The System tablespace is the first permanent tablespace created when a new database is created. It contains important data dictionary information to help the database run. The Sysaux tablespace is an auxiliary tablespace of the System tablespace, stores metadata of various Oracle Applications.

Ii. Table space Classification

Permanent tablespace and temporary tablespace.

System tablespace: replace System with Sysaux tablespace. The System tablespace is the core tablespace of the System. It stores data dictionary tables, control files, and data file information. This tablespace can only be used by users such as SYS and SYSTEM, and cannot be renamed or deleted.

Temporary tablespace: When order by, group by, or index creation is performed, temporary tablespace is used to store data, the server process first stores data in the global PGA of the process. When the space of the PGA is insufficient, a temporary segment is created. The temporary data is stored in the temporary tablespace. If no temporary tablespace is specified, the SYSTEM tablespace is used as the temporary tablespace by default, which leads to a large amount of storage fragments in the SYSTEM tablespace, which is not conducive to the stability of the database.

UNDO tablespace (UNDO tablespace and redo tablespace): UNDO management is implemented. For example, when a transaction is rolled back, old data is retrieved from the UNDO tablespace for restoration. Multiple Undo tablespaces can be created, but only one undo tablespace can be activated at a time, which is specified in UNDO_TABLESPACE.

Large file tablespace: a data file that can contain 4G data blocks is mainly used in ultra-large databases to reduce the number of data files.

User tablespace (data table space): stores user-created objects and private information.