Extent management in Oracle Database

Extent indicates the range.

In Oracle databases, extent management provides extent management local (local management) and extent management dictionary (data dictionary management)

The default value is local.

There are two ways to increase the size of each type:

Uniform: The default value is 1 MB. It is the default value in the temp tablespace, but cannot be applied in the undo tablespace.

The local tablespace management greatly improves the management efficiency and database performance compared with the dictionary tablespace management. Its advantages are as follows:

1. Reduced recursive space management

The local tablespace is managed and allocated by yourself, rather than the tablespace managed by the system like a dictionary, the local tablespace maintains a bitmap in each data file of the tablespace to track the remaining space and usage of the block in the file. And make updates in a timely manner. This update only modifies the table space quota and does not perform any update operations on other data dictionary tables. Therefore, no rollback information is generated, which greatly reduces space management and improves management efficiency. At the same time, the local tablespace can be managed in a UNIFORM size allocation mode (UNIFORM), which greatly reduces space management and improves database performance.

2. the system automatically manages the extents size or uses the unified extents size.

The local tablespace can be automatically allocated (AUTOALLOCATE) or uniformly distributed (UNIFORM). The system determines the size of extents automatically, the unified size allocation (UNIFORM) is the extents size specified by the user. Both methods improve the space management efficiency.

3. Reduced competition between data dictionaries

Because the local tablespace is managed by maintaining a bitmap of each data file to track and update the space of the blocks in the file, this update only modifies the quota of the tablespace, other data dictionary tables are not involved, which greatly reduces competition between data dictionary tables and improves database performance.

4. No rollback information is generated.

In addition to updating the table space quota, the local tablespace space management does not modify any other data dictionary tables. Therefore, no rollback information is generated, which greatly improves the running speed of the database.

5. No need to merge adjacent spaces

Because the extents space management of local tablespace management will automatically track the adjacent residual space and be automatically managed by the system, so there is no need to merge adjacent residual space. At the same time, all extents for local tablespace management can have the same size, which reduces space fragmentation.

6. Reduced Space fragments

7. Better management of temporary tablespace

Autoallocate:

You can convert a tablespace from dictionary extent management to local extent management

And back with the Oracle-supplied PL/SQL package DBMS_SPACE_ADMIN. The SYSTEM

Tablespace and any temporary tablespaces, however, cannot be converted from local to

Older style dictionary managem

The two extent management methods can be converted to each other, using PL/SQL DBMS_SPACE_ADMIN

However, system tablespace and temporary tablespace cannot be converted from local management to dictionary management.