虽然只使用了60685,从下面的输出可以推断出60684、60686、60687也被格式化了:
***dba_extents
SYS@tstdb1-SQL> col owner format a10
SYS@tstdb1-SQL> col segment_name format a20
SYS@tstdb1-SQL> set linesize 100
SYS@tstdb1-SQL> select owner,segment_name,relative_fno,block_id,blocks from dba_extents where segment_name='T1118_1'
OWNER SEGMENT_NAME RELATIVE_FNO BLOCK_ID BLOCKS
---------- -------------------- ------------ ---------- ----------
SCOTT T1118_1 5 60680 8
***dbms_space.space_usage过程的执行结果显示有5个Data block被formatted了
SYS@tstdb1-SQL> declare
2 unformatted_blocks NUMBER;
3 unformatted_bytes NUMBER;
4 fs1_blocks NUMBER;
5 fs1_bytes NUMBER;
6 fs2_blocks NUMBER;
7 fs2_bytes NUMBER;
8 fs3_blocks NUMBER;
9 fs3_bytes NUMBER;
10 fs4_blocks NUMBER;
11 fs4_bytes NUMBER;
12 full_blocks NUMBER;
13 full_bytes NUMBER;
14 begin
15 dbms_space.space_usage('SCOTT','T1118_1','TABLE',unformatted_blocks,unformatted_bytes,fs1_blocks,fs1_bytes,fs2_blocks,fs2_bytes,fs3_blocks,fs3_bytes,fs4_blocks,fs4_bytes,full_blocks,full_bytes);
16 dbms_output.put_line('unformatted_blocks:'||unformatted_blocks);
17 dbms_output.put_line('fs1_blocks:'||fs1_blocks);
18 dbms_output.put_line('fs2_blocks:'||fs2_blocks);
19 dbms_output.put_line('fs3_blocks:'||fs3_blocks);
20 dbms_output.put_line('fs4_blocks:'||fs4_blocks);
21 dbms_output.put_line('full_blocks:'||full_blocks);
22 end;
23 /
unformatted_blocks:0
fs1_blocks:0
fs2_blocks:0
fs3_blocks:0
fs4_blocks:4 <---从60683开始有5个block被格式化过了
full_blocks:1
***发起对scott.t1118_1的查询
SYS@tstdb1-SQL> select * from scott.t1118_1 where username='SYS';
USERNAME USER_ID CREATED
------------------------------ ---------- -----------------
SYS 0 20141110 21:16:12
SYS@tstdb1-SQL> select obj,indx,addr,hladdr,flag,lru_flag,class,state,dbarfil,dbablk,ba,tch from x$bh where obj=41231
OBJ INDX ADDR HLADDR FLAG LRU_FLAG CLASS STATE DBARFIL DBABLK BA TCH
---------- ---------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ----------
41231 31787 000000011099E2F8 07000001B6B503D8 35651584 0 4 1 5 60682 070000014A920000 8
41231 59572 000000011099E2F8 07000001BCD31B80 35651584 0 1 1 5 60687 07000000FF9F0000 2
41231 104826 000000011099E2F8 07000001BCDA4770 35651584 0 1 1 5 60684 07000000E2816000 2
41231 150065 000000011099E2F8 07000001B6C811F8 35651584 0 9 1 5 60681 07000000F4010000 2
41231 177953 000000011099E2F8 07000001BCE62A68 35651584 0 1 1 5 60686 07000000E754E000 2
41231 223088 000000011099E2F8 07000001B6D3F4F0 524288 0 1 3 5 60683 0700000105276000 1
41231 223089 000000011099E1B0 07000001B6D3F4F0 35651584 0 1 1 5 60683 07000001084A2000 5
41231 223090 000000011099E068 07000001B6D3F4F0 524288 2 1 3 5 60683 0700000162014000 1
41231 280093 000000011099E2F8 07000001B6DB20E0 35651584 0 8 1 5 60680 0700000102904000 2
41231 324995 000000011099E2F8 07000001BCF93888 524288 0 1 3 5 60685 0700000138622000 1
41231 324996 000000011099E1B0 07000001BCF93888 524288 0 1 3 5 60685 07000000EC7B0000 1
41231 324997 000000011099E068 07000001BCF93888 35651584 0 1 1 5 60685 07000000E74CC000 1
commit;
再次查询后682~687每个block的TCH都加了1,虽然60684、60686、60687三个block里未存放数据但因为在HWM以下所以也被scan到