不明白header_block与block_id

来源：互联网发布：安卓数据库删除数据编辑：程序博客网时间：2024/04/29 11:05

desc dba_extents
Name          Type       Nullable Default Comments
--------------- ------------ -------- ------- ---------------------------------------------------------
OWNER          VARCHAR2(30) Y             Owner of the segment associated with the extent
SEGMENT_NAME VARCHAR2(81) Y             Name of the segment associated with the extent
PARTITION_NAME  VARCHAR2(30) Y             Partition/Subpartition Name, if any, of the segment
SEGMENT_TYPE VARCHAR2(18) Y             Type of the segment
TABLESPACE_NAME VARCHAR2(30) Y             Name of the tablespace containing the extent
EXTENT_ID    NUMBER    Y             Extent number in the segment
FILE_ID       NUMBER    Y             Name of the file containing the extent
BLOCK_ID       NUMBER    Y             Starting block number of the extent
BYTES          NUMBER    Y             Size of the extent in bytes
BLOCKS       NUMBER    Y             Size of the extent in ORACLE blocks
RELATIVE_FNO NUMBER    Y             Relative number of the file containing the segment header

---------------------------------------------------------------------------------------------------------------

desc dba_segments
Name          Type       Nullable Default Comments
--------------- ------------ -------- ------- --------------------------------------------------------------------------------------------------------------------------------------
OWNER          VARCHAR2(30) Y             Username of the segment owner
SEGMENT_NAME VARCHAR2(81) Y             Name, if any, of the segment
PARTITION_NAME  VARCHAR2(30) Y             Partition/Subpartition Name, if any, of the segment
SEGMENT_TYPE VARCHAR2(18) Y             Type of segment:  "TABLE", "CLUSTER", "INDEX", "ROLLBACK",
"DEFERRED ROLLBACK", "TEMPORARY","SPACE HEADER", "TYPE2 UNDO"
or "CACHE"
TABLESPACE_NAME VARCHAR2(30) Y             Name of the tablespace containing the segment
HEADER_FILE    NUMBER    Y             ID of the file containing the segment header
HEADER_BLOCK NUMBER    Y             ID of the block containing the segment header
BYTES          NUMBER    Y             Size, in bytes, of the segment
BLOCKS       NUMBER    Y             Size, in Oracle blocks, of the segment
EXTENTS       NUMBER    Y             Number of extents allocated to the segment
INITIAL_EXTENT  NUMBER    Y             Size, in bytes, of the initial extent of the segment
NEXT_EXTENT    NUMBER    Y             Size, in bytes, of the next extent to be allocated to the segment
MIN_EXTENTS    NUMBER    Y             Minimum number of extents allowed in the segment
MAX_EXTENTS    NUMBER    Y             Maximum number of extents allowed in the segment
PCT_INCREASE NUMBER    Y             Percent by which to increase the size of the next extent to be allocated
FREELISTS    NUMBER    Y             Number of process freelists allocated in this segment
FREELIST_GROUPS NUMBER    Y             Number of freelist groups allocated in this segment
RELATIVE_FNO NUMBER    Y             Relative number of the file containing the segment header
BUFFER_POOL    VARCHAR2(7)  Y             The default buffer pool to be used for segments blocks

一个segment会有多个extent

一个segment在dba_segments中只有1条记录，只有一个header_block，这是存储segment header信息的那个block

一个segment在dba_extents中可能会有多条记录，每个block_id都是这个extent的起始block id。

一个ASSM的segment最初的那个extent中，block_id标示的block（也就是第一个extent的第一个block）是FIRST LEVEL BITMAP BLOCK，第二个block是SECOND LEVEL BITMAP BLOCK，这两个block用于管理free block，取代freelist的功能，而第三个block才是PAGETABLE SEGMENT HEADER（这才是header_block字段表示的block）。

----------------------------------------------------------------------------------------------------------------------------------------

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_BLOCK,EXTENTS from dba_segments where lower(SEGMENT_NAME)='personnel' order by OWNER;

OWNER                  SEGMENT_NAME    SEGMENT_TYPE       TABLESPACE_NAME          HEADER_BLOCK    EXTENTS
------------------------------ -------------------- ------------------ ------------------------------ ------------ ----------------------- -------------
LIJIWEI                        PERSONNEL            TABLE                                MYDB                                11                     1
SYS                            PERSONNEL            TABLE                                MYDB                                 27                    1

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BLOCK_ID,BLOCKS from dba_extents WHERE lower(SEGMENT_NAME)='personnel' order by OWNER;

OWNER                  SEGMENT_NAME    SEGMENT_TYPE       TABLESPACE_NAME                 BLOCK_ID     BLOCKS
------------------------------ -------------------- ------------------ ------------------------------ ---------- ----------------------- ---------------
LIJIWEI                        PERSONNEL            TABLE                              MYDB                                    9                   8
SYS                            PERSONNEL            TABLE                               MYDB                                   25                  8

SQL>