............................417792 Last Used Ext FileId....................6 Last Used Ext BlockId...................1152 Last Used Block.........................77
PL/SQL procedure successfully completed.
注意，这里77+51=128，正好是最后分配的一个extent的大小
--查看测试表ZLM1占用extent和block的情况 SQL> select block_id,extent_id,bytes,blocks from dba_extents where segment_name like 'ZLM%';
BLOCK_ID EXTENT_ID BYTES BLOCKS ---------- ---------- ---------- ---------- 128 0 65536 8 136 1 65536 8 144 2 65536 8 152 3 65536 8 160 4 65536 8 168 5 65536 8 176 6 65536 8 184 7 65536 8 192 8 65536 8 200 9 65536 8 208 10 65536 8 216 11 65536 8 224 12 65536 8 232 13 65536 8 240 14 65536 8 248 15 65536 8 256 16 1048576 128 --从256块起，开始分配1M的空间作为1个extent 384 17 1048576 128 512 18 1048576 128 640 19 1048576 128 768 20 1048576 128 896 21 1048576 128 1024 22 1048576 128 1152 23 1048576 128
从block_id字段的值可以发现，当在表插入数据后，分配给表的block从128开始一直到1152，并且，开始的1-16个extent会以8个块（8*8=64K）为分配单位，而到了第17个extent之后，则以128个块（8*128=1M）为分配单位，目前ZLM1表共分配了24个extent
--查看测试表ZLM1的block分配情况 SQL> col segment_name for a10 SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name like 'ZLM%';
SEGMENT_NA HEADER_FILE HEADER_BLOCK BLOCKS BYTES EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE ---------- ----------- ------------ ---------- ---------- ---------- ----------- ----------- ------------------ ZLM1 6 130 1152 9437184 24 1 2147483645 TABLE
这里可以发现一个规律，dba_segments中的header_block的值总是要比dba_extents查出来值的多2个块
--创建测试表ZLM2并查看block分配情况 SQL> create table zlm.zlm2 as select * from zlm.zlm1;
Table created.
SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name like 'ZLM%';
SEGMENT_NA HEADER_FILE HEADER_BLOCK BLOCKS BYTES EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE ---------- ----------- ------------ ---------- ---------- ---------- ----------- ----------- ------------------ ZLM2 6 1282 1152 9437184 24 1 2147483645 TABLE ZLM1 6 130 1152 9437184 24 1 2147483645 TABLE
第2个表从1282个块开始分配，为什么是1282呢？1282=130+1152，也就是从测试表ZLM1之后的block开始分配
--delete测试表ZLM1中的数据 SQL> delete from zlm.zlm1;
75541 rows deleted.
SQL> commit;
Commit complete.
SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED ------------------------------ ---------- ---------- ------------ ----------- ------------------ ZLM1 75541 1101 51 100 27-SEP-14
--分析测试表ZLM1和ZLM2并查看各自数据块占用情况 SQL> analyze table zlm.zlm1 compute statistics;
Table analyzed.
SQL> analyze table zlm.zlm2 compute statistics;
Table analyzed.
SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED ------------------------------ ---------- ---------- ------------ ----------- ------------------ ZLM1 0 1101 51 0 27-SEP-14 ZLM2 75541 1101 51 100 27-SEP-14
删除ZLM1表中数据以后，NUM_ROWS值为0，但是还是占用了1152个数据块，说明此时高水位并没有下降，仍然为1152
--truncate测试表ZLM1 SQL> truncate table zlm.zlm1;
Table truncated.
SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_ty |