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
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
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)。
一个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
------------------------------ -------------------- ------------------ ------------------------------ ------------ ----------------------- -------------
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
------------------------------ -------------------- ------------------ ------------------------------ ---------- ----------------------- ---------------
LIJIWEI PERSONNEL TABLE MYDB 9 8
SYS PERSONNEL TABLE MYDB 25 8
SQL>