Usage of Oracle Block-白红宇

Usage of Oracle Block

阅读量：6081 次

发布时间：2019-06-20

本文共 11910 字，大约阅读时间需要 39 分钟。

插入（Insert），更新（Update），删除（Delete）操作对数据块的影响

创建一个名为test的表，并向里面插入三条记录用于测试。用dba用户登录创建PL/SQL过程show_space，用于显示某个表使用block的信息。show_space的使用参考文章：http://blog.csdn.net/huang_tg/archive/2010/07/09/5724499.aspx

SQL> create table test

2 (sno varchar2(10),

3 sname varchar2(20));

表已创建。

SQL> insert into test values ('001','huang');

SQL> insert into test values ('002','ting');

SQL> insert into test values ('003','guang');

SQL> commit;

提交完成。

SQL> set serveroutput on

SQL> exec show_space('TEST');

Total Blocks............................3

Total Bytes.............................12288

Unused Blocks...........................1

Unused Bytes............................4096

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

Last Used Ext BlockId...................61573

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

PL/SQL 过程已成功完成。

SQL> alter system dump datafile 1 block 61574;

系统已更改。

data_block_dump

===============

tsiz: 0xfa0

hsiz: 0x18

pbl: 0x0296105c

bdba: 0x0040f086

flag=-------------

ntab=1

nrow=3

frre=-1

fsbo=0x18

fseo=0xf7a

avsp=0xf62

tosp=0xf62

0xe:pti[0] nrow=3 offs=0 --本块存在3条记录

0x12:pri[0] offs=0xf93 --记录的起始物理位置

0x14:pri[1] offs=0xf87

0x16:pri[2] offs=0xf7a

block_row_dump:

tab 0, row 0, @0xf93 --第一行数据开始的物理地址

tl: 13 fb: --H-FL-- lb: 0x1 cc: 2 --lb:表示属于XID 0x1 cc：表示有2字段

col 0: [ 3] 30 30 31 --字段1长度为3，数据30 30 31

col 1: [ 5] 68 75 61 6e 67 --字段2长度为5，数据68 75 51 6e 67

tab 0, row 1, @0xf87

tl: 12 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 3] 30 30 32

col 1: [ 4] 74 69 6e 67

tab 0, row 2, @0xf7a

tl: 13 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 3] 30 30 33

col 1: [ 5] 67 75 61 6e 67

end_of_block_dump

End dump data blocks tsn: 0 file#: 1 minblk 61574 maxblk 61574

1.插入（Insert）数据对数据块的影响

数据在oracle数据库中的存储是从block的底部开始的，遵循从下到上的规则。当一个新行被插入时,oracle首先搜索freelist (数据块中使用的空间未达到PCTUSED设置的值)中的空闲块，如果找到则插入数据。没有找到则使用HWM指向的数据块(block)，同时移动HWM，使其指向下一个未使用过的数据块。如果行数据过大，一个数据块装不下，那么就会产生行链接。以下是向测试表中插入两条记录后数据块的变化：

SQL> insert into test values ('004','tang');

SQL> insert into test values ('005','yan');

SQL> commit;

提交完成。

data_block_dump

===============

0xe:pti[0] nrow=5 offs=0 --增加了2条记录

0x12:pri[0] offs=0xf93

0x14:pri[1] offs=0xf87

0x16:pri[2] offs=0xf7a

0x18:pri[3] offs=0xf6e --新增加的004号记录

0x1a:pri[4] offs=0xf63 --新增加的005号记录

block_row_dump:

tab 0, row 3, @0xf6e --第4条记录开始地址

tl: 12 fb: --H-FL-- lb: 0x2 cc: 2

col 0: [ 3] 30 30 34 --字段1长度为3，数据为30 30 34

col 1: [ 4] 74 61 6e 67 --字段1长度为4，数据为74 61 6e 67

tab 0, row 4, @0xf63 --第5条记录开始地址

tl: 11 fb: --H-FL-- lb: 0x2 cc: 2

col 0: [ 3] 30 30 35 --字段1长度为3，数据为30 30 35

col 1: [ 3] 79 61 6e --字段1长度为3，数据为79 61 6e

end_of_block_dump

End dump data blocks tsn: 0 file#: 1 minblk 61574 maxblk 61574

2.删除（delete）数据对数据块的影响

删除(delete)：删除数据时，oracle并不回收被删除数据所占用的空间，只是做一个标记以表明，这部分空间可以重用。如果一个数据块因为删除了数据使得他的使用空间低于PCTUSED的值。那么这个数据块将被再次放到freelist中，当需要时就会被重用。一下是删除一条数据以后数据块中的变化：

SQL> delete from test where sno='002' or sno='003';

SQL> commit;

提交完成。

SQL> select * from test;

SNO SNAME

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

001 huang

004 tang

005 yan

SQL> exec show_space('TEST');

Total Blocks............................3

Total Bytes.............................12288

Unused Blocks...........................1

Unused Bytes............................4096

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

Last Used Ext BlockId...................61573

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

PL/SQL 过程已成功完成。

SQL> alter system dump datafile 1 block 61574;

系统已更改。

block_row_dump:

tab 0, row 1, @0xf87 --原002的记录位置，已经没有数据

tl: 2 fb: --HDFL-- lb: 0x1

tab 0, row 2, @0xf7a --原003的记录位置，已经没有数据

tl: 2 fb: --HDFL-- lb: 0x1

end_of_block_dump

End dump data blocks tsn: 0 file#: 1 minblk 61574 maxblk 61574

SQL> insert into test values ('002','t');

SQL> insert into test values ('003','g');

SQL> commit;

提交完成。

SQL> alter system dump datafile 1 block 61574;

系统已更改。

block_row_dump:

tab 0, row 1, @0xf87 --被删除的002号记录

tl: 2 fb: --HDFL-- lb: 0x1

tab 0, row 2, @0xf7a --被删除的003号记录

tl: 2 fb: --HDFL-- lb: 0x1

tab 0, row 5, @0xf5a --新增加的002号记录

tl: 9 fb: --H-FL-- lb: 0x2 cc: 2

col 0: [ 3] 30 30 32 --新插入的002号记录的数据

col 1: [ 1] 74

tab 0, row 6, @0xf51 --新增加的003号记录

tl: 9 fb: --H-FL-- lb: 0x2 cc: 2

col 0: [ 3] 30 30 33 --新插入的003号记录的数据

col 1: [ 1] 67

end_of_block_dump

End dump data blocks tsn: 0 file#: 1 minblk 61574 maxblk 61574

3.更新（update）数据对数据块的影响

更新(update)：数据被更新时，oracle首先使用数据所在数据块(block)所预留的空闲空间(PCTFREE)来存储数据更新可能带来的行数据增大。如果更新产生的新数据过大，造成数据块(block)无法装下新的数据，那么将被拷贝到一个有足够空间的数据块中，而原始块则会存储一个指向更新够新数据的开始地址。以下是更新两条记录以后数据块的变化：

SQL> update test set sname='ting' where sno='002';

SQL> update test set sname='guang' where sno='003';

SQL> commit;

提交完成。

SQL> alter system dump datafile 1 block 61574;

系统已更改。

block_row_dump:

tab 0, row 5, @0xed6 --第5行记录（新插入的002号记录）

tl: 12 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 3] 30 30 32

col 1: [ 4] 74 69 6e 67 --数据已经被更新

tab 0, row 6, @0xec9 --第6行记录（新插入的003号记录）

tl: 13 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 3] 30 30 33

col 1: [ 5] 67 75 61 6e 67 --数据已经被更新

end_of_block_dump

End dump data blocks tsn: 0 file#: 1 minblk 61574 maxblk 61574

#####################################################

SHOW_SPACE是TOM写的一个小工具，SHOW_SPACE实际上就是一个存储过程，这个存储过程可以用来分析空间按使用情况，十分的方便。以下为SHOW_SPACE的脚本：

create or replace procedure show_space

( p_segname in varchar2,

p_owner in varchar2 default user,

p_type in varchar2 default 'TABLE',

p_partition in varchar2 default NULL )

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 )

begin

dbms_output.put_line( rpad(p_label,40,'.') || p_num );

end;

begin

dbms_space.unused_space

( segment_owner => p_owner,

segment_name => p_segname,

segment_type => p_type,

partition_name => p_partition,

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( '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;

此工具的使用方法为：

SQL> create table t as select * from all_users;

表已创建。

SQL> set serveroutput on;

SQL> exec show_space('T');

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

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

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

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

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

Last Used Ext BlockId...................60745

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

PL/SQL 过程已成功完成。

不过此版本只适合表空间为非ASSM的时候，当表空间为ASSM时不能用，因为DBMS_SPACE.FREE_BLOCKS不允许在ASSM上操作。要想在ASSM表空间的时候也能使用则可以使用以下版本：

create or replace procedure show_space

( p_segname_1 in varchar2,

p_space in varchar2 default 'MANUAL',

p_type_1 in varchar2 default 'TABLE' ,

p_analyzed in varchar2 default 'N',

p_owner_1 in varchar2 default user)

p_segname varchar2(100);

p_type varchar2(10);

p_owner varchar2(30);

l_unformatted_blocks number;

l_unformatted_bytes number;

l_fs1_blocks number;

l_fs1_bytes number;

l_fs2_blocks number;

l_fs2_bytes number;

l_fs3_blocks number;

l_fs3_bytes number;

l_fs4_blocks number;

l_fs4_bytes number;

l_full_blocks number;

l_full_bytes number;

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 )

begin

dbms_output.put_line( rpad(p_label,40,'.') ||

p_num );

end;

begin

p_segname := upper(p_segname_1);

p_owner := upper(p_owner_1);

p_type := p_type_1;

if (p_type_1 = 'i' or p_type_1 = 'I') then

p_type := 'INDEX';

end if;

if (p_type_1 = 't' or p_type_1 = 'T') then

p_type := 'TABLE';

end if;

if (p_type_1 = 'c' or p_type_1 = 'C') then

p_type := 'CLUSTER';

end if;

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 );

if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then

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 );

p( 'Free Blocks', l_free_blks );

end if;

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 );

/*IF the segment is analyzed */

if p_analyzed = 'Y' then

dbms_space.space_usage(segment_owner => p_owner ,

segment_name => p_segname ,

segment_type => p_type ,

unformatted_blocks => l_unformatted_blocks ,

unformatted_bytes => l_unformatted_bytes,

fs1_blocks => l_fs1_blocks,

fs1_bytes => l_fs1_bytes ,

fs2_blocks => l_fs2_blocks,

fs2_bytes => l_fs2_bytes,

fs3_blocks => l_fs3_blocks ,

fs3_bytes => l_fs3_bytes,

fs4_blocks => l_fs4_blocks,

fs4_bytes => l_fs4_bytes,

full_blocks => l_full_blocks,

full_bytes => l_full_bytes);

dbms_output.put_line(rpad(' ',50,'*'));

dbms_output.put_line('The segment is analyzed');

p( '0% -- 25% free space blocks', l_fs1_blocks);

p( '0% -- 25% free space bytes', l_fs1_bytes);

p( '25% -- 50% free space blocks', l_fs2_blocks);

p( '25% -- 50% free space bytes', l_fs2_bytes);

p( '50% -- 75% free space blocks', l_fs3_blocks);

p( '50% -- 75% free space bytes', l_fs3_bytes);

p( '75% -- 100% free space blocks', l_fs4_blocks);

p( '75% -- 100% free space bytes', l_fs4_bytes);

p( 'Unused Blocks', l_unformatted_blocks );

p( 'Unused Bytes', l_unformatted_bytes );

p( 'Total Blocks', l_full_blocks);

p( 'Total bytes', l_full_bytes);

end if;

end;

以下是此版本的SHOW_SPACE的使用方法简介：

ASSM 类型的表

SQL> exec show_space('t','auto');

Total Blocks............................512

Total Bytes.............................4194304

Unused Blocks...........................78

Unused Bytes............................638976

Last Used Ext FileId....................9

Last Used Ext BlockId...................25608

Last Used Block.........................50

PL/SQL procedure successfully completed.

ASSM 类型的索引

SQL> exec show_space('t_index','auto','i');

Total Blocks............................80

Total Bytes.............................655360

Unused Blocks...........................5

Unused Bytes............................40960

Last Used Ext FileId....................9

Last Used Ext BlockId...................25312

Last Used Block.........................3

PL/SQL procedure successfully completed.

对analyze 过的segment 可以这样

SQL> exec show_space('t','auto','T','Y');

Total Blocks............................512

Total Bytes.............................4194304

Unused Blocks...........................78

Unused Bytes............................638976

Last Used Ext FileId....................9

Last Used Ext BlockId...................25608

Last Used Block.........................50

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............0

25% -- 50% free space bytes.............0

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free space blocks...........0

75% -- 100% free space bytes............0

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................418

Total bytes.............................3424256

PL/SQL procedure successfully completed

转载于:https://blog.51cto.com/zekoo/1742755

你可能感兴趣的文章

springMVC拦截配置

查看>>

将不确定变为确定~LINQ查询包含对不同数据上下文上所定义项的引用

查看>>

Cmockery macro demo hacking

查看>>

Android NIO(Noblocking I/O非阻塞I/O)小结

查看>>

为什么机器学习难于应用

查看>>

算法: skiplist 跳跃表代码实现和原理

CSS3与页面布局学习总结（七）——前端预处理技术（Less、Sass、CoffeeScript、TypeScript）...

OVS local network 连通性分析 - 每天5分钟玩转 OpenStack（132）

查看>>

Django 表单校验表单字段设置自定义表单校验规则

查看>>

TeamViewer“试用期已到期”解决方法

查看>>

SQL Server 2014如何提升非在线的在线操作

查看>>

成为MySQL DBA博客-性能配置调优

查看>>

【java开发系列】—— spring简单入门示例

查看>>

无人驾驶，敢问路在何方？

查看>>

轻松实现日志可视化？— 95后阿里云 MVP 王鹏翰的答案

查看>>

Maven实战（六）--- dependencies与dependencyManagement的区别

查看>>