blocks,extents,segments

<pre name="code" class="sql"><pre name="code" class="sql">Block: Oracle存储的最小单位

Extent：

EXTENT_ID NUMBER    Extent number in the segment

BLOCK_ID  NUMBER    Starting block number of the extent

BLOCKS  NUMBER    Size of the extent in Oracle block

SQL> set linesize 200
SQL> select owner,segment_name,extent_id,file_id,block_id,blocks from dba_extents  where owner='TEST' and segment_name='BFW_TEST';

OWNER            SEGMENT_NAME                   EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
------------------------------ --------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
TEST             BFW_TEST                       0      6       5376  128
TEST             BFW_TEST                       1      6       1792  128
TEST             BFW_TEST                       2      6       1920  128
TEST             BFW_TEST                       3      6       2048  128
TEST             BFW_TEST                       4      6       2176  128
TEST             BFW_TEST                       5      6       3712  128
TEST             BFW_TEST                       6      6       3840  128
TEST             BFW_TEST                       7      6       3968  128
TEST             BFW_TEST                       8      6       4096  128

已选择9行。


select owner,segment_name,header_file,header_block,blocks,extents from dba_segments where owner='TEST' and segment_name='BFW_TEST'SQL> SQL> ;

OWNER            SEGMENT_NAME                  HEADER_FILE HEADER_BLOCK     BLOCKS  EXTENTS
------------------------------ --------------------------------------------------------------------------------- ----------- ------------ ---------- ----------
TEST             BFW_TEST                        6       5376 1152        9

HEADER_FILE NUMBER    ID of the file containing the segment header

HEADER_BLOCK  NUMBER    ID of the block containing the segment header


BLOCKS  NUMBER    Size, in Oracle blocks, of the segment  --segment的块数 = 128 * 9

EXTENTS NUMBER    Number of extents allocated to the segment

SQL> select owner,segment_name,extent_id,file_id,block_id,blocks from dba_extents  where owner='TEST' and segment_name='TEST';


OWNER            SEGMENT_NAME                   EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
------------------------------ --------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
TEST             TEST                       0     10      15232  128
TEST             TEST                       1      6      18432  128
TEST             TEST                       2     10      15360  128
TEST             TEST                       3      6      19200  128
TEST             TEST                       4     10      15488  128
TEST             TEST                       5      6      19328  128
TEST             TEST                       6     10      15616  128
TEST             TEST                       7      6      19456  128
TEST             TEST                       8     10      15744  128

但是segment上的block不一定连续,extent 不能跨数据文件(因为extent都是连续的block)
segment不可以跨表空间但可以跨数据文件