block存储数据的基本结构

1 先创建show_space存储过程

2 生成测试数据

3 查看block里面数据的基本结构

这部分比较简单，是个引子。

1 先创建show_space存储过程：
create or replace procedure show_space
(
p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE'
)
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||p_num );
end;
begin
dbms_space.free_blocks
( segment_owner => p_owner,segment_name => p_segname,segment_type => p_type,freelist_group_id => 0,free_blks => l_free_blks);

dbms_space.unused_space
( segment_owner => p_owner,segment_name => p_segname,segment_type => p_type,total_blocks => l_total_blocks,
total_bytes => l_total_bytes,unused_blocks => l_unused_blocks,unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );

p( 'Free Blocks', l_free_blks );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/

2 生成测试数据：

SQL> create table t1
2 (id number(4,0),
3 name varchar2(10)
4 addr varchar2(10))
5 ;

表已创建。

SQL> insert into t1
2 select number,'aaabbbcc'||rownum,'liuqj'||rownum from all_tables
3 where rownum < 15;

已创建14行。

SQL> commit;

提交完成。

SQL> exec show_space('T1')
Free Blocks.............................1

Total Blocks............................8

Total Bytes.............................65536

Unused Blocks...........................6

Unused Bytes............................49152

Last Used Ext FileId....................1

Last Used Ext BlockId...................60649

Last Used Block.........................2

PL/SQL 过程已成功完成。

3 查看block里面数据的基本结构：

SQL>alter system dump datafile 1 block 60650;

系统已更改。

查看trace文件：
*** 2007-05-10 12:22:24.000
Start dump data blocks tsn: 0 file#: 1 minblk 60650 maxblk 60650
buffer tsn: 0 rdba: 0x0040ecea (1/60650)
scn: 0x0000.00211886 seq: 0x01 flg: 0x02 tail: 0x18860601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0040ecea
Object id on Block? Y
seg/obj: 0x814d csc: 0x00.211885 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.02e.0000083f 0x0080067a.00a2.35 --U- 15 fsc 0x0000.00211886
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

data_block_dump,data header at 0x305105c
===============
tsiz: 0x1fa0
hsiz: 0x30
pbl: 0x0305105c
bdba: 0x0040ecea
76543210
flag=--------
ntab=1
nrow=15
frre=-1
fsbo=0x30
fseo=0x1e3b
avsp=0x1e0b
tosp=0x1e0b
0xe:pti[0] nrow=15 offs=0
0x12:pri[0] offs=0x1e3b
0x14:pri[1] offs=0x1e52
0x16:pri[2] offs=0x1e69
0x18:pri[3] offs=0x1e80
0x1a:pri[4] offs=0x1e97
0x1c:pri[5] offs=0x1eae
0x1e:pri[6] offs=0x1ec5
0x20:pri[7] offs=0x1edc
0x22:pri[8] offs=0x1ef3
0x24:pri[9] offs=0x1f0a
0x26:pri[10] offs=0x1f23
0x28:pri[11] offs=0x1f3c
0x2a:pri[12] offs=0x1f55
0x2c:pri[13] offs=0x1f6e
0x2e:pri[14] offs=0x1f87
block_row_dump:
tab 0, row 0, @0x1e3b
tl: 23 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [ 9] 61 61 61 62 62 62 63 63 31
col 2: [ 6] 6c 69 75 71 6a 31
tab 0, row 1, @0x1e52
tl: 23 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 03
col 1: [ 9] 61 61 61 62 62 62 63 63 32
col 2: [ 6] 6c 69 75 71 6a 32
tab 0, row 2, @0x1e69
tl: 23 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 04
col 1: [ 9] 61 61 61 62 62 62 63 63 33
col 2: [ 6] 6c 69 75 71 6a 33
tab 0, row 3, @0x1e80
tl: 23 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 05
col 1: [ 9] 61 61 61 62 62 62 63 63 34
col 2: [ 6] 6c 69 75 71 6a 34
tab 0, row 4, @0x1e97
tl: 23 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 06
col 1: [ 9] 61 61 61 62 62 62 63 63 35
col 2: [ 6] 6c 69 75 71 6a 35
tab 0, row 5, @0x1eae
tl: 23 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 07
col 1: [ 9] 61 61 61 62 62 62 63 63 36
col 2: [ 6] 6c 69 75 71 6a 36
tab 0, row 6, @0x1ec5
tl: 23 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 08
col 1: [ 9] 61 61 61 62 62 62 63 63 37
col 2: [ 6] 6c 69 75 71 6a 37
tab 0, row 7, @0x1edc
tl: 23 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 09
col 1: [ 9] 61 61 61 62 62 62 63 63 38
col 2: [ 6] 6c 69 75 71 6a 38
tab 0, row 8, @0x1ef3
tl: 23 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 0a
col 1: [ 9] 61 61 61 62 62 62 63 63 39
col 2: [ 6] 6c 69 75 71 6a 39
tab 0, row 9, @0x1f0a
tl: 25 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 0b
col 1: [10] 61 61 61 62 62 62 63 63 31 30
col 2: [ 7] 6c 69 75 71 6a 31 30
tab 0, row 10, @0x1f23
tl: 25 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 0c
col 1: [10] 61 61 61 62 62 62 63 63 31 31
col 2: [ 7] 6c 69 75 71 6a 31 31
tab 0, row 11, @0x1f3c
tl: 25 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 0d
col 1: [10] 61 61 61 62 62 62 63 63 31 32
col 2: [ 7] 6c 69 75 71 6a 31 32
tab 0, row 12, @0x1f55
tl: 25 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 0e
col 1: [10] 61 61 61 62 62 62 63 63 31 33
col 2: [ 7] 6c 69 75 71 6a 31 33
tab 0, row 13, @0x1f6e
tl: 25 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 0f
col 1: [10] 61 61 61 62 62 62 63 63 31 34
col 2: [ 7] 6c 69 75 71 6a 31 34
tab 0, row 14, @0x1f87
tl: 25 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 10
col 1: [10] 61 61 61 62 62 62 63 63 31 35
col 2: [ 7] 6c 69 75 71 6a 31 35
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 60650 maxblk 60650

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/593324/viewspace-376201/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/593324/viewspace-376201/