Behavior of the block when delete and shrink space. ASSM

Table contains 9 extents.


SQL> select extents from dba_segments where segment_name like 'MCZIM_TEST_BLOCK';

EXTENTS
----------
9

Block fully contained data

Dump of First Level Bitmap Block
 --------------------------------
 nbits : 4 nranges: 1 parent dba: 0xa0c00104 poffset: 8
 unformatted: 0 total: 64 first useful block: 4
 owning instance : 1
 instance ownership changed at 04/09/2013 17:43:04
 Last successful Search 04/09/2013 17:43:04
 Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

Extent Map Block Offset: 4294967295
 First free datablock : 64
 Bitmap block lock opcode 0
 Locker xid: : 0x0000.000.00000000
 Dealloc scn: 1479554104.77
 Flag: 0x00000000 (-/-/-/-/-/-)
 Inc #: 0 Objd: 317262
 --------------------------------------------------------
 DBA Ranges :
 --------------------------------------------------------
 0xa0c00300 Length: 64 Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:Metadata
 4:FULL 5:FULL 6:FULL 7:FULL
 8:FULL 9:FULL 10:FULL 11:FULL
 12:FULL 13:FULL 14:FULL 15:FULL
 16:FULL 17:FULL 18:FULL 19:FULL
 20:FULL 21:FULL 22:FULL 23:FULL
 24:FULL 25:FULL 26:FULL 27:FULL
 28:FULL 29:FULL 30:FULL 31:FULL
 32:FULL 33:FULL 34:FULL 35:FULL
 36:FULL 37:FULL 38:FULL 39:FULL
 40:FULL 41:FULL 42:FULL 43:FULL
 44:FULL 45:FULL 46:FULL 47:FULL
 48:FULL 49:FULL 50:FULL 51:FULL
 52:FULL 53:FULL 54:FULL 55:FULL
 56:FULL 57:FULL 58:FULL 59:FULL
 60:FULL 61:FULL 62:FULL 63:FULL
 --------------------------------------------------------
End dump data blocks tsn: 632 file#: 643 minblk 768 maxblk 768

Block after partial deleting data

Dump of First Level Bitmap Block
 --------------------------------
 nbits : 4 nranges: 1 parent dba: 0xa0c00104 poffset: 8
 unformatted: 0 total: 64 first useful block: 4
 owning instance : 1
 instance ownership changed at 04/09/2013 17:43:04
 Last successful Search 04/09/2013 17:43:04
 Freeness Status: nf1 0 nf2 0 nf3 0 nf4 60

Extent Map Block Offset: 4294967295
 First free datablock : 4
 Bitmap block lock opcode 0
 Locker xid: : 0x0000.000.00000000
 Dealloc scn: 1479554104.77
 Flag: 0x00000000 (-/-/-/-/-/-)
 Inc #: 0 Objd: 317262
 --------------------------------------------------------
 DBA Ranges :
 --------------------------------------------------------
 0xa0c00300 Length: 64 Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:Metadata
 4:75-100% free 5:75-100% free 6:75-100% free 7:75-100% free
 8:75-100% free 9:75-100% free 10:75-100% free 11:75-100% free
 12:75-100% free 13:75-100% free 14:75-100% free 15:75-100% free
 16:75-100% free 17:75-100% free 18:75-100% free 19:75-100% free
 20:75-100% free 21:75-100% free 22:75-100% free 23:75-100% free
 24:75-100% free 25:75-100% free 26:75-100% free 27:75-100% free
 28:75-100% free 29:75-100% free 30:75-100% free 31:75-100% free
 32:75-100% free 33:75-100% free 34:75-100% free 35:75-100% free
 36:75-100% free 37:75-100% free 38:75-100% free 39:75-100% free
 40:75-100% free 41:75-100% free 42:75-100% free 43:75-100% free
 44:75-100% free 45:75-100% free 46:75-100% free 47:75-100% free
 48:75-100% free 49:75-100% free 50:75-100% free 51:75-100% free
 52:75-100% free 53:75-100% free 54:75-100% free 55:75-100% free
 56:75-100% free 57:75-100% free 58:75-100% free 59:75-100% free
 60:75-100% free 61:75-100% free 62:75-100% free 63:75-100% free
 --------------------------------------------------------
End dump data blocks tsn: 632 file#: 643 minblk 768 maxblk 768

Block after executing shrink space

Dump of First Level Bitmap Block
 --------------------------------
 nbits : 4 nranges: 1 parent dba: 0xa0c00104 poffset: 8
 unformatted: 0 total: 64 first useful block: 4
 owning instance : 1
 instance ownership changed at 04/09/2013 17:43:04
 Last successful Search 04/09/2013 17:43:04
 Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0

Extent Map Block Offset: 4294967295
 First free datablock : 64
 Bitmap block lock opcode 0
 Locker xid: : 0x0000.000.00000000
 Dealloc scn: 1479554104.77
 Flag: 0x00000000 (-/-/-/-/-/-)
 Inc #: 0 Objd: 317262
 --------------------------------------------------------
 DBA Ranges :
 --------------------------------------------------------
 0xa0c00300 Length: 64 Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:Metadata
 4:FULL 5:FULL 6:FULL 7:FULL
 8:FULL 9:FULL 10:FULL 11:FULL
 12:FULL 13:FULL 14:FULL 15:FULL
 16:FULL 17:FULL 18:FULL 19:FULL
 20:FULL 21:FULL 22:FULL 23:FULL
 24:FULL 25:FULL 26:FULL 27:FULL
 28:FULL 29:FULL 30:FULL 31:FULL
 32:FULL 33:FULL 34:FULL 35:FULL
 36:FULL 37:FULL 38:FULL 39:FULL
 40:FULL 41:FULL 42:FULL 43:FULL
 44:FULL 45:FULL 46:FULL 47:FULL
 48:FULL 49:FULL 50:FULL 51:FULL
 52:FULL 53:FULL 54:FULL 55:FULL
 56:FULL 57:FULL 58:FULL 59:FULL
 60:FULL 61:FULL 62:FULL 63:FULL
 --------------------------------------------------------
End dump data blocks tsn: 632 file#: 643 minblk 768 maxblk 768

The shrink space moves data to the header block and neighbouring blocks and moves HWM. Now table contains 3 extents. The same information in the header block and in the dictionary.

SQL> select extents from dba_segments where segment_name like 'MCZIM_TEST_BLOCK';

EXTENTS
----------
3

 

Extent Control Header
 -----------------------------------------------------------------
 Extent Header:: spare1: 0 spare2: 0 #extents: 3 #blocks: 768
 last map 0x00000000 #maps: 0 offset: 1356
 Highwater:: 0xa0c00355 ext#: 2 blk#: 85 ext size: 256
 #blocks in seg. hdr's freelists: 0
 #blocks below: 583
 mapblk 0x00000000 offset: 2
 Unlocked
 --------------------------------------------------------
 Low HighWater Mark :
 Highwater:: 0xa0c00106 ext#: 0 blk#: 6 ext size: 256
 #blocks in seg. hdr's freelists: 0
 #blocks below: 0
 mapblk 0x00000000 offset: 0
 Level 1 BMB for High HWM block: 0xa0c00301
 Level 1 BMB for Low HWM block: 0xa0c00100
 --------------------------------------------------------
 Segment Type: 1 nl2: 1 blksz: 4096 fbsz: 0
 L2 Array start offset: 0x00000994
 First Level 3 BMB: 0x00000000
 L2 Hint for inserts: 0xa0c00104
 Last Level 1 BMB: 0xa0c00303
 Last Level II BMB: 0xa0c00104
 Last Level III BMB: 0x00000000
 Map Header:: next 0x00000000 #extents: 3 obj#: 317262 flag: 0x10000000
 Inc # 1
 Extent Map
 -----------------------------------------------------------------
 0xa0c00100 length: 256
 0xa0c00200 length: 256
 0xa0c00300 length: 256

 Auxillary Map
 --------------------------------------------------------
 Extent 0 : L1 dba: 0xa0c00100 Data dba: 0xa0c00106
 Extent 1 : L1 dba: 0xa0c00200 Data dba: 0xa0c00204
 Extent 2 : L1 dba: 0xa0c00300 Data dba: 0xa0c00304
 --------------------------------------------------------

 Second Level Bitmap block DBAs
 --------------------------------------------------------
 DBA 1: 0xa0c00104

End dump data blocks tsn: 632 file#: 643 minblk 261 maxblk 261
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s