oracle表碎片查询整理过程_Oracle

oracle表碎片查询整理

模拟插入数据

create table test as select * from dba_objects;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
commit;

收集统计信息

set time on
exec dbms_stats.gather_table_stats(ownname=>'test',tabname=> 'test',degree=>2);

查询初始表的碎片率68126

select table_name,
round ( (blocks * 8), 2) "hw_space k",
round ( (num_rows * avg_row_len / 1024), 2) "real_space k",
round ( (blocks * 10 / 100) * 8, 2) "(pctfree) k",
round (
( blocks * 8
- (num_rows * avg_row_len / 1024)
- blocks * 8 * 10 / 100),
2)
"waste_space k"
from user_tables
where temporary = 'n' and table_name='test'
--and owner='test'
order by 5 desc;

table_name hw_space k real_space k (pctfree) k waste_space k
---------- ---------- ------------ ----------- -------------
test            89176     68126.08      8917.6      12132.32

第一次模拟清理数据

delete from test where object_type in('synonym','java class');
commit;

再次收集统计信息

set time on
exec dbms_stats.gather_table_stats(ownname=>'test',tabname=> 'test',degree=>2);

第一次清理完查询表的碎片率18963

table_name hw_space k real_space k (pctfree) k waste_space k
---------- ---------- ------------ ----------- -------------
test            89176     18963.03      8917.6      61295.37

第二次模拟删除数据

delete from test where object_type in('view','index','table','type');
commit;

再次收集统计信息

set time on
exec dbms_stats.gather_table_stats(ownname=>'test',tabname=> 'test',degree=>2);

第二次清理完查询表的碎片率6199

但是可以看到浪费的空间一直在增加74058.9，高水位一直没有变化89176

table_name hw_space k real_space k (pctfree) k waste_space k
---------- ---------- ------------ ----------- -------------
test            89176       6199.5      8917.6       74058.9

进行表碎片整理

15:21:46 sql> alter table test enable row movement;

table altered.

15:24:52 sql> alter table test shrink space;

table altered.

15:25:23 sql> exec dbms_stats.gather_table_stats(ownname=>'test',tabname=> 'test',degree=>2);

pl/sql procedure successfully completed.

15:25:47 sql> select table_name,
15:25:56   2  round ( (blocks * 8), 2) "hw_space k",
15:25:56   3  round ( (num_rows * avg_row_len / 1024), 2) "real_space k",
15:25:56   4  round ( (blocks * 10 / 100) * 8, 2) "(pctfree) k",
15:25:56   5  round (
15:25:56   6  ( blocks * 8
15:25:56   7  - (num_rows * avg_row_len / 1024)
15:25:56   8  - blocks * 8 * 10 / 100),
2)
15:25:56   9  15:25:56  10  "waste_space k"
from user_tables
15:25:56  11  15:25:56  12  where temporary = 'n' and table_name='test'
15:25:56  13  --and owner='test'
15:25:56  14  order by 5 desc;

table_name hw_space k real_space k (pctfree) k waste_space k
---------- ---------- ------------ ----------- -------------
test             7408       6199.5       740.8         467.7

经过以上表碎片整理之后，可以看到表的高水位已经降下来7408，浪费的空间已经得到释放467.7