下面是一个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表并没有对数据进行修改。 |
truncate表操作，前后位图块信息变化：等您坐沙发呢！