oracle查看变,oracle数据块pctfree变化查看

思路：

1、创建一个表t1

2、通过t1表的segment的segment_header找到L2 -> L1从而查看L1中的pctfree变化

3、让数据块pctfree变化的方法是插入数据再删除数据

SQL> create table t1(name varchar2(1000)) tablespace test1;

Table created.

SQL> insert into t1 select rpad('A', 999, 'A') from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select OWNER, SEGMENT_NAME, HEADER_FILE, HEADER_BLOCK from dba_segments where SEGMENT_NAME='T1' and owner='TEST';

OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK

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

TEST T1 9 146

SQL> alter system dump datafile 9 block 146;

System altered.通过dump文件信息找到L2块号

Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0

L2 Array start offset:  0x00001434

First Level 3 BMB:  0x00000000

L2 Hint for inserts:  0x02400091

Last Level 1 BMB:  0x02400090

Last Level II BMB:  0x02400091

Last Level III BMB:  0x00000000

Map Header:: next  0x00000000  #extents: 1    obj#: 17370  flag: 0x10000000

Inc # 0

从上面可以看到L2块号是 0x91 -> 145(十进制)

再dump 9号文件145号块

SQL> alter system dump datafile 9 block 146;

System altered.下面是对我有用的信息

Dump of Second Level Bitmap Block

number: 1       nfree: 1       ffree: 0      pdba:     0x02400092

Inc #: 0 Objd: 17370

opcode:0

xid:

L1 Ranges :

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

0x02400090  Free: 5 Inst: 1

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

End dump data blocks tsn: 8 file#: 9 minblk 145 maxblk 145

从上面我们看到L1号块时 0x90 -> 144(十进制)

再dump 9号文件144号块

SQL> alter system dump datafile 9 block 144;

System altered.下面是对我们有用的信息

Dump of First Level Bitmap Block

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

nbits : 4 nranges: 1         parent dba:  0x02400091   poffset: 0

unformatted: 0       total: 8         first useful block: 3

owning instance : 1

instance ownership changed at 08/24/2014 18:42:49

Last successful Search 08/24/2014 18:42:49

Freeness Status:  nf1 0      nf2 0      nf3 0      nf4 5  --nf4 5 ：代表 <75-100% free>类型的快有5个

Extent Map Block Offset: 4294967295

First free datablock : 3

Bitmap block lock opcode 0

Locker xid:     :  0x0000.000.00000000

Dealloc scn: 0.0

Flag: 0x00000001 (-/-/-/-/-/HWM)

Inc #: 0 Objd: 17370

HWM Flag: HWM Set

Highwater::  0x02400098  ext#: 0      blk#: 8      ext size: 8

#blocks in seg. hdr's freelists: 0

#blocks below: 5

mapblk  0x00000000  offset: 0

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

DBA Ranges :

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

0x02400090  Length: 8      Offset: 0

0:Metadata   1:Metadata   2:Metadata 3:75-100% free

4:75-100% free   5:75-100% free   6:75-100% free   7:75-100% free --看出有五块的pctfree都是在 <75-100% free>类型中

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

这时我们向表t1中插入数据查看 以上两处的变化

SQL> insert into t1 select rpad('B', 999, 'B') from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 9 block 144;

System altered.Dump of First Level Bitmap Block

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

nbits : 4 nranges: 1         parent dba:  0x02400091   poffset: 0

unformatted: 0       total: 8         first useful block: 3

owning instance : 1

instance ownership changed at 08/24/2014 18:42:49

Last successful Search 08/24/2014 18:42:49

Freeness Status:  nf1 0      nf2 0

nf3 1      nf4 4

--看到有一个块变化了在nf3类型中<50-75% free>

Extent Map Block Offset: 4294967295

First free datablock : 3

Bitmap block lock opcode 0

Locker xid:     :  0x0000.000.00000000

Dealloc scn: 0.0

Flag: 0x00000001 (-/-/-/-/-/HWM)

Inc #: 0 Objd: 17370

HWM Flag: HWM Set

Highwater::  0x02400098  ext#: 0      blk#: 8      ext size: 8

#blocks in seg. hdr's freelists: 0

#blocks below: 5

mapblk  0x00000000  offset: 0

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

DBA Ranges :

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

0x02400090  Length: 8      Offset: 0

0:Metadata   1:Metadata   2:Metadata

3:75-100% free

4:75-100% free   5:75-100% free   6:75-100% free

7:50-75% free--看到一个块pctfree已经发生变化

下面我们再删除一行看看块的pctfree变化

SQL> delete from t1 where name like '%B%';

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 9 block 144;

System altered.下面是对我们有用的信息

Dump of First Level Bitmap Block

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

nbits : 4 nranges: 1         parent dba:  0x02400091   poffset: 0

unformatted: 0       total: 8         first useful block: 3

owning instance : 1

instance ownership changed at 08/24/2014 18:42:49

Last successful Search 08/24/2014 18:42:49

Freeness Status:  nf1 0      nf2 0      nf3 0      nf4 5 --变回原来的样子

Extent Map Block Offset: 4294967295

First free datablock : 3

Bitmap block lock opcode 0

Locker xid:     :  0x0000.000.00000000

Dealloc scn: 0.0

Flag: 0x00000001 (-/-/-/-/-/HWM)

Inc #: 0 Objd: 17370

HWM Flag: HWM Set

Highwater::  0x02400098  ext#: 0      blk#: 8      ext size: 8

#blocks in seg. hdr's freelists: 0

#blocks below: 5

mapblk  0x00000000  offset: 0

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

DBA Ranges :

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

0x02400090  Length: 8      Offset: 0

0:Metadata   1:Metadata   2:Metadata   3:75-100% free    4:75-100% free   5:75-100% free   6:75-100% free   7:75-100% free--pctfree变回原来的样子