Oracle zone management and segment space management

As a DBA, one of the common scenarios is:

Create a tablespace:

Copy codeThe Code is as follows: createtablespaceThink
Datafile '/u01/app/oracle/oradata/orcl/think. dbf' size100M
Autoextendonnext10Mmaxsize4096M
Extentmanagementlocaluniformsize256K
Segmentspacemanagementauto;

Create a user:Copy codeThe Code is as follows: createuserbin
DefaulttablespaceThink
Temporarytablespacetemp;

Grant permissions:Copy codeThe Code is as follows: grantconnect, resourcetobin;
Revokeunlimitedtablespacefrombin;
AlteruserbinquotaunlimitedonThink;

In this scenario, there are two statements:
① Extentmanagementlocaluniformsize256K
② Segmentspacemanagementauto
The former is zone management, and the latter is segment space management.

(I) Zone Management
Zone management is actually the management of tablespaces.
Before 8i, The tablespace is managed in a dictionary. when an object is created or deleted, Oracle's space allocation or recycling is recorded and managed in a data dictionary.
In high-concurrency systems, this will lead to performance degradation, space fragmentation, and other problems
This is already an old technology. I will not go into details here.
Starting from 8i, Oracle introduced local tablespace Management
Adds a bitmap area to the header of the data file in each tablespace.
The first block in the first partition of a segment is firstlevelbitmapblock.
The second block is secondlevelbitmapblock.
The third block is the segment header block.
These two blocks are used to manage freeblocks.

Syntax:Copy codeCode: extentmanagementlocal {autoallocate | uniformsizenK/M}

Automatic allocation or uniform size
For automatic allocation, Oracle allocates space according to the incremental algorithm.
If you select a uniform size, you can also specify the size of each interval in detail.
Dba_extents this view shows which objects are allocated with how many intervals

(Ii) segment space management
Oracle allocates space to object segments in units of segments, while segments use and manage space in units of blocks.
We use several parameters to understand section space management.Copy codeThe Code is as follows: [SQL]
<Spanstyle = "font-size: 18px;"> sys @ ORCL> selectextent_management, segment_space_managementfromdba_tablespaces;
EXTENT_MANSEGMEN
----------------
LOCALMANUAL
LOCALMANUAL
LOCALAUTO
LOCALMANUAL
LOCALMANUAL

From 9i, there are two types of segment space management:
① MSSM: You can set freelists, freelistgroups, pctused, pctfree, initrans, and other parameters to control how to allocate and use space in segments.
② ASSM: you only need to control the pctfree parameter. Other parameters will be ignored even if they are created.
(1) freelist
When using MSSM tablespace management, Oracle will maintain blocks below HWM for objects with free space in freelist
Freelist and freelistgroup do not exist in the ASSM tablespace. This technology is used only in the MSSM tablespace.
(2) pctfree and pctused
Pctfree tells Oracle how much space should be reserved on the block to complete future updates.
For MSSM, she controls when the block is put into freelist and when it is retrieved from freelist.
If it is greater than pctfree, the block will remain on freelist.
For ASSM, ASSM does not use freelist at all. In ASSM, pctused is also ignored.
However, she will still limit whether a new row can be inserted into a block.
Setting pctfree appropriately helps reduce row migration
(3) initrans
Whether it is ASSMorMSSM, this parameter is still valid.
The initialization size of the transaction slot in the block header is specified by the initrans of the object.