Oracle案例：truncate表操作，前后位图块信息变化
我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
Oracle案例：truncate表操作，前后位图块信息变化
下面是一个dump表truncate操作前后的位图块的信息，原因是同事在问，truncate表，到底在对象所在的块更改了那些内容
其实truncate表，主要是更改对象所在第一个区中的L1,L2,L3 3个块的值。对象的其它的块的信息是不会修改的
我们可以通过块的scn的值来判断
1，测试表创建与基本信息收集
SQL> create table scott.htz1 tablespace htz as select * from dba_objects where rownum<10000;
Table created.
SQL> @segment.sql
Enter value for owner: scott
Enter value for segment_name: htz1
Enter value for tablespace_name:
HEADER
OWNER:SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME FILE_BLOCK SIZE(M) BLOCKS EXTENTS
—————————— ——————– ————— ——————– ——————– ———- ———- ——-
SCOTT.HTZ1 TABLE HTZ 5.146 2 256 17
****************************** ———-
Total: 2
1 row selected.
SQL> @extent.sql
Enter value for owner: scott
Enter value for segment_name: htz1
Enter value for tablespace_name:
FILE BLOCK
OWNER:SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME ID FNO EXTENT_ID BEGIN_END BLOCKS BYTES(KB)
—————————— ——————– ————— ——————– —– —– ———- ————————- ——— ———
SCOTT.HTZ1 TABLE HTZ 5 5 0 144~151 8 64
TABLE HTZ 5 5 1 152~159 8 64
TABLE HTZ 5 5 2 160~167 8 64
TABLE HTZ 5 5 3 168~175 8 64
TABLE HTZ 5 5 4 176~183 8 64
TABLE HTZ 5 5 5 184~191 8 64
TABLE HTZ 5 5 6 192~199 8 64
TABLE HTZ 5 5 7 200~207 8 64
TABLE HTZ 5 5 8 208~215 8 64
TABLE HTZ 5 5 9 216~223 8 64
TABLE HTZ 5 5 10 224~231 8 64
TABLE HTZ 5 5 11 232~239 8 64
TABLE HTZ 5 5 12 240~247 8 64
TABLE HTZ 5 5 13 248~255 8 64
TABLE HTZ 5 5 14 256~263 8 64
TABLE HTZ 5 5 15 264~271 8 64
TABLE HTZ 5 5 16 384~511 128 1024
****************************** ——— ———
Total: 256 2048
SQL> alter system flush buffer_cache;
System altered.
这里可以看到htz1这个对象的块头在146这个块，一共占用17个区的信息
2，dump位图块与第一个数据块，第二个L1位图块
SQL> alter system dump datafile 5 block min 144 block max 147;
System altered.
SQL> alter system dump datafile 5 block 160;
System altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_29424.trc
3，truncate表
SQL> truncate table scott.htz1;
Table truncated.
SQL> alter system flush buffer_cache;
System altered.
SQL> @extent.sql
Enter value for owner: scott
Enter value for segment_name: htz1
Enter value for tablespace_name:
FILE BLOCK
OWNER:SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME ID FNO EXTENT_ID BEGIN_END BLOCKS BYTES(KB)
—————————— ——————– ————— ——————– —– —– ———- ————————- ——— ———
SCOTT.HTZ1 TABLE HTZ 5 5 0 144~151 8 64
****************************** ——— ———
Total: 8 64
这里可以看到只占用一个区的信息
4，再次dump位图块，第一个数据块，truncate前的第二个L1位图块
SQL> alter system dump datafile 5 block min 144 block max 147;
System altered.
SQL> alter system dump datafile 5 block 160;
System altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_29455.trc
SQL> !cp /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_29455.trc /soft/test
5，L1 位图块
truncate前
buffer tsn: 6 rdba: 0x01400090 (5/144)
scn: 0x0000.000ec1e9 seq: 0x04 flg: 0x04 tail: 0xc1e92004
frmt: 0x02 chkval: 0x6f7d type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of First Level Bitmap Block
——————————–
nbits : 4 nranges: 2 parent dba: 0x01400091 poffset: 0
unformatted: 0 total: 16 first useful block: 3
owning instance : 1
instance ownership changed at
Last successful Search
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295
First free datablock : 16
Bitmap block lock opcode 2
Locker xid: : 0x0004.013.000002ca
Dealloc scn: 966333.0
Flag: 0x00000000 (-/-/-/-/-/-)
Inc #: 0 Objd: 87355
——————————————————–
DBA Ranges :
——————————————————–
0x01400090 Length: 8 Offset: 0
0x01400098 Length: 8 Offset: 8
0:Metadata 1:Metadata 2:Metadata 3:FULL
4:FULL 5:FULL 6:FULL 7:FULL
8:FULL 9:FULL 10:FULL 11:FULL
12:FULL 13:FULL 14:FULL 15:FULL
——————————————————–
truncate后
buffer tsn: 6 rdba: 0x01400090 (5/144)
scn: 0x0000.000ec24b seq: 0x02 flg: 0x04 tail: 0xc24b2002
frmt: 0x02 chkval: 0x03c7 type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of First Level Bitmap Block
——————————–
nbits : 4 nranges: 1 parent dba: 0x01400091 poffset: 0
unformatted: 5 total: 8 first useful block: 3
owning instance : 1
instance ownership changed at
Last successful Search
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295
First free datablock : 3
Bitmap block lock opcode 9
Locker xid: : 0x0005.01c.00000361
Dealloc scn: 967243.0
Flag: 0x00000021 (OBJD/-/-/-/-/HWM)
Inc #: 0 Objd: 87356
HWM Flag: HWM Set
Highwater:: 0x01400093 ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr’s freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
——————————————————–
DBA Ranges :
——————————————————–
0x01400090 Length: 8 Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:unformatted
4:unformatted 5:unformatted 6:unformatted 7:unformatted
——————————————————–
这里我们可以看到l1 bmb变化的很多信息，主要是块空间的使用情况
6，L2 BMB块
truncate前
buffer tsn: 6 rdba: 0x01400091 (5/145)
scn: 0x0000.000ec1e9 seq: 0x09 flg: 0x04 tail: 0xc1e92109
frmt: 0x02 chkval: 0xd396 type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of Second Level Bitmap Block
number: 10 nfree: 2 ffree: 8 pdba: 0x01400092
Inc #: 0 Objd: 87355
opcode:0
xid:
L1 Ranges :
——————————————————–
0x01400090 Free: 1 Inst: 1
0x014000a0 Free: 1 Inst: 1
0x014000b0 Free: 1 Inst: 1
0x014000c0 Free: 1 Inst: 1
0x014000d0 Free: 1 Inst: 1
0x014000e0 Free: 1 Inst: 1
0x014000f0 Free: 1 Inst: 1
0x01400100 Free: 1 Inst: 1
0x01400180 Free: 5 Inst: 1
0x01400181 Free: 5 Inst: 1
——————————————————–
truncate 后
Dump of Second Level Bitmap Block
number: 1 nfree: 1 ffree: 0 pdba: 0x01400092
Inc #: 0 Objd: 87356
opcode:4
xid:
L1 Ranges :
——————————————————–
0x01400090 Free: 5 Inst: 1
——————————————————–
这里可以看到，truncate后，其实的l1 bmb全部已经释放了，只剩下一个L1 BMB信息
7，段头,L3 BMB的信息
truncate前
buffer tsn: 6 rdba: 0x01400092 (5/146)
scn: 0x0000.000ec1eb seq: 0x01 flg: 0x04 tail: 0xc1eb2301
frmt: 0x02 chkval: 0xff3d type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Extent Control Header
—————————————————————–
Extent Header:: spare1: 0 spare2: 0 #extents: 17 #blocks: 256
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0140018e ext#: 16 blk#: 14 ext size: 128
#blocks in seg. hdr’s freelists: 0
#blocks below: 142
mapblk 0x00000000 offset: 16
Unlocked
——————————————————–
Low HighWater Mark :
Highwater:: 0x0140018e ext#: 16 blk#: 14 ext size: 128
#blocks in seg. hdr’s freelists: 0
#blocks below: 142
mapblk 0x00000000 offset: 16
Level 1 BMB for High HWM block: 0x01400180
Level 1 BMB for Low HWM block: 0x01400180
——————————————————–
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01400091
Last Level 1 BMB: 0x01400181
Last Level II BMB: 0x01400091
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 17 obj#: 87355 flag: 0x10000000
Inc # 0
Extent Map
—————————————————————–
0x01400090 length: 8
0x01400098 length: 8
0x014000a0 length: 8
0x014000a8 length: 8
0x014000b0 length: 8
0x014000b8 length: 8
0x014000c0 length: 8
0x014000c8 length: 8
0x014000d0 length: 8
0x014000d8 length: 8
0x014000e0 length: 8
0x014000e8 length: 8
0x014000f0 length: 8
0x014000f8 length: 8
0x01400100 length: 8
0x01400108 length: 8
0x01400180 length: 128
Auxillary Map
——————————————————–
Extent 0 : L1 dba: 0x01400090 Data dba: 0x01400093
Extent 1 : L1 dba: 0x01400090 Data dba: 0x01400098
Extent 2 : L1 dba: 0x014000a0 Data dba: 0x014000a1
Extent 3 : L1 dba: 0x014000a0 Data dba: 0x014000a8
Extent 4 : L1 dba: 0x014000b0 Data dba: 0x014000b1
Extent 5 : L1 dba: 0x014000b0 Data dba: 0x014000b8
Extent 6 : L1 dba: 0x014000c0 Data dba: 0x014000c1
Extent 7 : L1 dba: 0x014000c0 Data dba: 0x014000c8
Extent 8 : L1 dba: 0x014000d0 Data dba: 0x014000d1
Extent 9 : L1 dba: 0x014000d0 Data dba: 0x014000d8
Extent 10 : L1 dba: 0x014000e0 Data dba: 0x014000e1
Extent 11 : L1 dba: 0x014000e0 Data dba: 0x014000e8
Extent 12 : L1 dba: 0x014000f0 Data dba: 0x014000f1
Extent 13 : L1 dba: 0x014000f0 Data dba: 0x014000f8
Extent 14 : L1 dba: 0x01400100 Data dba: 0x01400101
Extent 15 : L1 dba: 0x01400100 Data dba: 0x01400108
Extent 16 : L1 dba: 0x01400180 Data dba: 0x01400182
——————————————————–
Second Level Bitmap block DBAs
——————————————————–
DBA 1: 0x01400091
truncate 后
buffer tsn: 6 rdba: 0x01400092 (5/146)
scn: 0x0000.000ec270 seq: 0x03 flg: 0x04 tail: 0xc2702303
frmt: 0x02 chkval: 0xfc29 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Extent Control Header
—————————————————————–
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01400093 ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr’s freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Disk Lock:: Locked by xid: 0x0001.01c.0000028d
——————————————————–
Low HighWater Mark :
Highwater:: 0x01400093 ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr’s freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x01400090
Level 1 BMB for Low HWM block: 0x01400090
——————————————————–
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01400091
Last Level 1 BMB: 0x01400090
Last Level II BMB: 0x01400091
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 87356 flag: 0x10000000
Inc # 0
Extent Map
—————————————————————–
0x01400090 length: 8
Auxillary Map
——————————————————–
Extent 0 : L1 dba: 0x01400090 Data dba: 0x01400093
——————————————————–
Second Level Bitmap block DBAs
——————————————————–
DBA 1: 0x01400091
这里主要提一个HHWM,LHWM的值都发生了变化，所以说truncate释放了空间
8，第一个数据块
truncate前
Block header dump: 0x01400093
Object id on Block? Y
seg/obj: 0x1553b csc: 0x00.ec1c4 itc: 3 flg: E typ: 1 – DATA
brn: 0 bdba: 0x1400090 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.000ec1c4
0x02 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
bdba: 0x01400093
data_block_dump,data header at 0x2a97ed627c
===============
tsiz: 0x1f80
hsiz: 0xc2
pbl: 0x2a97ed627c
76543210
flag=——–
ntab=1
nrow=88
frre=-1
fsbo=0xc2
fseo=0x432
avsp=0x370
tosp=0x370
truncate后
Block header dump: 0x01400093
Object id on Block? Y
seg/obj: 0x1553b csc: 0x00.ec1c4 itc: 3 flg: E typ: 1 – DATA
brn: 0 bdba: 0x1400090 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.000ec1c4
0x02 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
bdba: 0x01400093
data_block_dump,data header at 0x2a97f6227c
===============
tsiz: 0x1f80
hsiz: 0xc2
pbl: 0x2a97f6227c
76543210
flag=——–
ntab=1
nrow=88
frre=-1
fsbo=0xc2
fseo=0x432
avsp=0x370
tosp=0x370
看到第1个数据块的scn值没有变化，说明truncate表并没有对数据进行修改。
------------------作者介绍-----------------------
姓名：黄廷忠
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
提供ORACLE技术支持(系统优化，故障处理，安装升级，数据恢复等） TEL:18081072613，微信、QQ同手机号。
浙公网安备 33010602011771号