Table-Space management

A tablespace is a logical organization of a database in which multiple table spaces can be created.

The main function of table space is to separate the data of different uses to improve the security of data and improve the performance of the system.
Tablespace plays a very important role in the database.

On the one hand, tablespaces consist of multiple segments in the logical structure, data is stored in the segment of the table space, and on the other hand, the tablespace corresponds to multiple data files in the physical structure, and the data in the database objects is ultimately stored in these data files.
There are many types of table spaces, and according to different classification criteria, different classification results will be obtained.

Table spaces can be divided into system tablespace, undo tablespace, temporary tablespace, and user table space, depending on the type of data stored.
Table spaces can be divided into dictionary management table spaces and local management table spaces, depending on how the storage space is stored.
Table spaces can be divided into large file table spaces and small file table spaces, depending on whether large files are supported.
Table space management mainly involves the creation of various types of table space, delete, extend, modify the state and so on.

Structure of Table spaces

A table space consists of multiple segments, each of which may represent a database object.

When a user creates a database object, such as a table, an index, a cluster, a segment is automatically created in the Tablespace to store the object's data.
One segment occupies one or more zones, and when the space of the zone is filled, the segments are automatically expanded.
when you create a tablespace, you need to specify how the extents are allocated .

District Management Mode

District management refers to how you allocate storage space for a database object, and you need to specify how you want the zone to be managed when you create the table space.
Oracle supports two types of zone management, Dictionary management, and local administration .
When you create a dictionary-managed tablespace, you need to specify several storage parameters, and later, when you create a database object in this tablespace, you assign the required extents to the database objects according to these storage parameters, and when they are full, the database server assigns the new extents to the database objects according to the storage parameters.
The storage parameters of the tablespace are recorded in the data dictionary.
When creating a dictionary-managed tablespace, you can specify several storage parameters:
•INITIAL (initial) : Specifies the size of the first area, which is the size of the extents to be allocated for the database object first.
•Next : Specifies the size of the next area.
• minextents : The minimum number of extents assigned to a database object , with a default value of 1.
• MAXEXTENTS : The maximum number of extents allocated for database objects .
• pctincrease : Starting from the third district, the percentage of each district that grows based on the previous area.
In the Dictionary management table space, the allocation and collection of the extents are based on the data dictionary.

When assigning extents to a database object, you need to query the storage parameters from the data dictionary, and after the allocation ends, you need to write the results of the assignment to a data dictionary.
This is done to perform a number of query operations and DML operations on the data dictionary, and to produce redo logs and rollback data.
In addition, because the size of the extents in the database objects is different, as the database server runs, more and more storage fragmentation will occur in the segment

In the Local management table space, the extents are the same size.

When you create a table space, you can specify a uniform area size by parameter, or the size of the zone automatically by the database server according to the actual situation.
The local management approach has the following advantages over dictionary management:
• Allocation and recycling of zones is no longer based on data dictionaries, which avoids recursive access to data dictionaries and does not result in redo logs and rollback data.
• All zones are the same size, which reduces storage fragmentation in storage space.
• You do not need to merge storage fragmentation in tablespace, the database server automatically monitors usage of storage space and merges adjacent free storage space.
Oracle recommends that you use local management table spaces.

Table spaces created in Oracle 11g databases are managed locally by default, as the system table space is currently managed by the dictionary and can be converted to a local management table space by invoking the stored procedure in the PL/SQL package in the following way:

EXECUTE dbms_space_admin.tablespace_migrate_to_local (' SYSTEM ');

Section Management method

When a user writes data to a table, the data is written to an idle chunk of data in the table Block , and the server process finds free blocks of data in the table, and Write the data to the data block, last also modify the state of the data block .
for segment space Management , Oracle provides two ways, one is manual management and the other is automatic management . The
way to manage segment space manually is to have a reserved space for each segment 's header , maintaining a free list in the reserved space to record all free blocks in the current segment in this list .
When a user writes data to a table, the server process looks for a sufficient number of free blocks in the idle list to write the data to those blocks.
If a block is already full , the block is removed from the free list .
in the process of finding free blocks , to prevent other server processes from using the same free block, the server process locks the idle list , and only the locks on the idle list are freed. Other server processes can find free blocks in the free list.
If a user has frequent transactions on a table, the idle list becomes a bottleneck that slows transaction execution.
Automatic Management is the default segment management method for Oracle 11g databases.

This is done in this way: there is a bitmap in the head of each segment, the state of each block in the current segment is recorded in the bitmap, the state of the data block is represented by the binary number 0 and 1, and each chunk occupies only one bit in the bitmap.
The benefit of using bitmaps is first to save space , and secondly, when the user writes data to the table, the server process can look up the free data block as long as the bitmap is scanned and does not need to locking the bitmap .
When you create a table space, you specify how you want to manage the space for the tablespace.

Data files

All data in a tablespace is stored on disk as a data file.

A table space contains one or more data files.
When you create a tablespace, you specify at least one data file for the table space.
If the tablespace contains only one data file, the data in the tablespace is stored in the data file.

If a tablespace contains more than one file, the data in the table space is distributed in the individual data files in the extents.
Assuming that there are three data files in a tablespace, the 第一、四、七 in the table when you create a table in that table space ... Area will be located in the first data file, 第二、五、八 、...... Area will be in the second data file, and so on.

If the data file is stored on a different disk, users can read and write multiple data files at the same time while accessing data in the table space, thereby reducing the number of collisions between disk reads and writes.
When the storage space in the tablespace is exhausted, the user will no longer be able to write to the data.

The database administrator must extend the tablespace to allocate new storage space for it.
You can add new data files to a tablespace, or you can manually extend an existing data file, and you can also activate the automatic extension of the data file to allow it to scale automatically.

Management of the local management table space

Management of Table spaces