Oracle tablespace space management

 

Table space: Provides the logical structure of the space used by the database. Its physical structure is a data file. A table space can contain multiple data files.

Local table space management (LMT)-a new table space management mode that appears after 8i. It uses local bitmap to manage the space usage of the table space.

Dictionary-Managed table space (DMT)-A table space management mode that can be used before and after 8i. You can use the data Dictionary to manage the space usage of the table space.

Segment: A logical structure of a database, such as table Segment, index Segment, and rollback Segment. The Segment exists in the tablespace and corresponds to a certain storage space.

Range (Extent for short)-the storage of segments can be divided into one or more intervals, each of which occupies a certain number of data blocks. In the locally managed tablespace, the Extent of the tablespace corresponds to the Extent of the corresponding segment.

Block: The minimum storage unit of the database. In this article, the Block size convention is 8192 bytes.

Bit: the space management unit for local tablespace management. A single digit may be equal to one interval, or multiple digits may form one interval.

Origins of local tablespace Management

In Oracle8I, Oracle introduced a brand new tablespace management method: localized tablespace management. The so-called localization management means that Oracle no longer uses a data dictionary table to record the usage of the partitions in the Oracle tablespace, but adds a bitmap area to the header of the data file in each tablespace, the usage of each zone is recorded. Whenever a zone is used or released for reuse, Oracle updates this record in the data file header to reflect this change.

Create a local tablespace: (local tablespace management is used by default after oracle 10 Gb)

Syntax: create tablespace name

DATAFILE 'data file details'

[Extent management {LOCAL

{AUTOALLOCATE | UNIFORM [size inteter [K | M]}]

The keyword extent management local specifies that this is a LOCAL-managed tablespace. For system tablespace, you can only specify extent mangement local when creating a database because it is the first tablespace created during database creation.

Advantages:

1. The tablespace managed by localization avoids recursive space management operations. This situation often occurs in the tablespace managed by the data dictionary. When the usage of the partition in the tablespace changes, the information of the table in the data dictionary changes, the rollback segments in the system tablespace are also used.

2. The tablespace managed locally avoids the information of idle space and used space written into the corresponding table of the data dictionary. This reduces the competition between the data dictionary tables and improves the concurrency of space management.

3. Zone localization management automatically tracks idle blocks in the tablespace, reducing the need to manually merge free space.

4. The partition size in the tablespace can be determined by the Oracle system, or the database administrator can specify a uniform size to avoid the fragmentation problem of the dictionary tablespace.

5. Change the idle block from the data dictionary management to the header record of the data file to manage the idle block. In this way, rollback information is not generated and the rollback segments in the system tablespace are no longer used. If the data dictionary is used for management, it records the relevant information in the data dictionary table to generate rollback information.

Because of the preceding features of the tablespace, it supports more concurrent operations in a tablespace and reduces the dependency on the data dictionary.

Oracle converts a dictionary-managed tablespace to a local-managed tablespace

Dictionary management tablespaces check their data dictionaries whenever tables or other objects need to be expanded to ensure available space is allocated to objects, allocate a new section to the object and update its available space information.

Local management tablespace stores the space management information of the data file itself, and the tablespace automatically tracks the available or used status of each data file block.

In databases with many transactions, it is clear that dictionary management checks the data dictionary every time data is inserted, which causes database performance loss.

Conversion:

The first method is command transfer.

First, you need to create an oracle tablespace. After 10 Gb of oracle, local tablespace management is used by default.

Use commands for table space Transfer

Alter table temp move tablespace new_temp;

You need to rebuild the index

Alter table index rebuild tablespace new_index;