SQL> select rowid,deptno from dept;

ROWID DEPTNO

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

AAAMfNAAEAAAAAQAAA 10

AAAMfNAAEAAAAAQAAB 20

AAAMfNAAEAAAAAQAAC 30

AAAMfNAAEAAAAAQAAD 40

ROWID是一个伪列，该列的值并没有真正的保存在数据块中，但是可以查询并显示出来。

ROWID的格式 OOOOOO FFF BBBBBB RRR ,

OOOOOO表示行所在的对象号，

FFF表示行所在文件号，

BBBBBB表示该行所在的数据块号，

RRR表示行在数据块中的行号，

rowid采用64进制的18位字符来表示。

SQL> select dbms_rowid.rowid_block_number(rowid) from dept;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

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

16

SQL> select deptno,

rowid,

dbms_rowid.rowid_object(rowid) object_id,

dbms_rowid. rowid_relative_fno(rowid) file_id,

dbms_rowid.rowid_block_number(rowid) block_id,

dbms_rowid.rowid_row_number(rowid) num

from dept;

DEPTNO ROWID OBJECT_ID FILE_ID BLOCK_ID NUM

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

10 AAAMfNAAEAAAAAQAAA 51149 4 16 0

20 AAAMfNAAEAAAAAQAAB 51149 4 16 1

30 AAAMfNAAEAAAAAQAAC 51149 4 16 2

40 AAAMfNAAEAAAAAQAAD 51149 4 16 3

SQL> create table my_objects as select * from dba_objects;

表已创建。

SQL> select count(*) from my_objects;

COUNT(*)

----------

50383

SQL> select SUM(bytes/1024/1024) from dba_extents where segment_name='MY_OBJECTS';

SUM(BYTES/1024/1024)

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

6

SQL> analyze table my_objects compute statistics;

表已分析。

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables d where table_name=

upper('my_objects');

TABLE_NAME BLOCKS EMPTY_BLOCKS

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

MY_OBJECTS 712 56

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from my_objects;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))

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

692

--补充这个查询不包括段头，与位图空间块

通过查询表分析的BLOCKS 与dba_tables的数据快是差不多。相差为10。说明不存在高水位问题。现在

模拟数据删除。

SQL> delete from my_objects where object_name like '%C%';

已删除18101行。

SQL> delete from my_objects where object_name like '%U%';

已删除4696行。

SQL> delete from my_objects where object_name like '%A%';

已删除6989行。

SQL> commit;

提交完成。

SQL> select count(*) from my_objects;

COUNT(*)

----------

20597

SQL> select SUM(bytes/1024/1024) from dba_extents where segment_name='MY_OBJECTS';

SUM(BYTES/1024/1024)

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

6

发现空间并没有被释放。

SQL> analyze table my_objects compute statistics;

表已分析。

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables d where table_name=

2 upper('my_objects');

TABLE_NAME BLOCKS EMPTY_BLOCKS

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

MY_OBJECTS 712 56

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from my_objects

;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))

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

633

此时发现查询出来的两个BLOCKS 相差有点大了。而实际上数据已经删除了快3万条。而我们并没有频繁

去删除。现在我们把表进行 shrink

SQL> alter table my_objects enable row movement;

表已更改。

SQL> alter table my_objects shrink space;

表已更改。

SQL> analyze table my_objects compute statistics;

表已分析。

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from my_objects

;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))

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

290

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables d where table_name=

2 upper('my_objects');

TABLE_NAME BLOCKS EMPTY_BLOCKS

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

MY_OBJECTS 290 14

SQL> select SUM(bytes/1024/1024) from dba_extents where segment_name='MY_OBJECTS

';

SUM(BYTES/1024/1024)

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

2.375

此时我们已经发现表分析后BLOCKS两个相差不多了而且空间也由原来的6M释放出来了。

现在我们模拟对这个表进行大量 insert delete 看看情况。我们借用JOB向dba_objects表中

随机抽取5000条数据插入到my_objects。然后又在这个表中进行3组并行的 delete数据

create or replace procedure pro_del_myobjects

as

begin

delete from my_objects where object_name like '%C%';

delete from my_objects where object_name like '%U%';

delete from my_objects where object_name like '%A%';

commit;

end;

create or replace procedure pro_del as

cursor cur_myobjects is select object_id from my_objects sample block (10) where

rownum<1000;

v_myobjects my_objects.object_id%type;

begin

open cur_myobjects;

fetch cur_myobjects into v_myobjects ;

for i in 1.. 1000 loop

delete from my_objects where object_id =v_myobjects;

end loop;

commit;

close cur_myobjects;

end;

declare

v number;

begin

dbms_job.submit(job => v,what => 'insert into my_objects select * from (select * from

dba_objects order by dbms_random.value) where rownum <5000; commit;',

next_date => sysdate,interval => 'sysdate+1/1440');

end;

declare

v number;

begin

dbms_job.submit(v,

'PRO_DEL_MYOBJECTS;',

sysdate,

'sysdate+2/1440');

end;

declare

v number;

begin

dbms_job.submit(v,

'pro_del;',

sysdate,

'sysdate+5/1440');

end;

declare

v number;

begin

dbms_job.submit(v,'delete from my_objects where rownum<1000;

commit;',sysdate,'sysdate+1/1440');

end;

--小知识

随机抽取数据有几种方法

利用 dbms_random.value

select * from dba_objects order by dbms_random.value where rou_num<50 ；随机抽50条

还可以利用 sample 随机抽取my_object 10% 中的记录10条

select * from my_objects sample block(10) where rownum<10；

还有一个参数 send 固定返回值

select * from my_objects sample block(10) seed(10) where rownum<10；

SQL> analyze table my_objects compute statistics;

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from my_objects;

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from dba_tables d where table_name=upper

('my_objects');

SQL> select SUM(bytes/1024/1024) from dba_extents where segment_name='MY_OBJECTS';

SQL> select count(*) from my_objects

SQL> alter table my_objects enable row movement;

SQL> alter table my_objects shrink space;

通过实时对比只要对表进行分析发现两个blocks 就完全相同了。

而利用shrink space 能做到对他们blocsk更加精确了。

ITPub博客

rowid解释与shrink space测试表空间释放问题

最新文章