在Oracle运维的过程中，会经常遇到说表碎片严重的问题。表碎片严重会导致出现数据库的性能问题，在sql访问数据的时候需要消耗更多的资源，因此常常出现在数据库的优化中。想要获取表碎片的情况，计算方式和处理方式很重要。
1.计算表的碎片情况
- 查看某个用户下表的碎片情况，查询信息的准确性依赖于统计信息的准确性，并不能反映表的碎片率。
set lines 199 pagesize 199
set COLSEP '|'
select d.owner,
d.table_name,
round((d.blocks * 8) / 1024, 2) "allocated MB",
round((d.num_rows * d.avg_row_len / 1024 / 1024), 2) "used MB",
round((d.blocks * 10 / 100) * 8 / 1024, 2) "reserved(d.pct_free) MB",
round((d.blocks * 8 - (d.num_rows * d.avg_row_len / 1024) -d.blocks * 8 * 10 / 100) / 1024,2) "waste_MB"
from dba_tables d
where d.blocks * 8 / 1024 > 10
-- and d.owner = 'SCOTT'
order by 6 desc;
- 使用脚本时替换上面注释掉的条件
OWNER |TABLE_NAME |allocated MB| used MB|reserved(d.pct_free) MB| waste_MB
--------|---------------|------------|----------|-----------------------|----------
SYS |IDL_UB1$ | 266.02| .9| 26.6| 238.52
SYS |IDL_UB2$ | 30.66| .28| 3.07| 27.32
SYS |COLLECTION$ | 23.27| .07| 2.33| 20.87
SYS |TYPE$ | 23.27| .22| 2.33| 20.72
SYS |ATTRIBUTE$ | 23.27| .73| 2.33| 20.21
SYS |RESULT$ | 18.03| .13| 1.8| 16.1
SYS |METHOD$ | 18.03| .18| 1.8| 16.05
SYS |PARAMETER$ | 18.03| .69| 1.8| 15.54
SYS |JAVA$MC$ | 12.15| .09| 1.21| 10.85
SYS |VIEWTRCOL$ | 11.15| 0| 1.11| 10.03
SYS |OPQTYPE$ | 11.15| .01| 1.11| 10.03
输出项说明如下：
"allocated MB" 代表实际的大小
"used MB" 真正使用的大小
"reserved(d.pct_free) MB" 代表保留的大小，一般都是默认10%
"waste_MB" 代表浪费的空间
如果想查看具体某张表的碎片情况，可以把where条件中的OWNER='SCOTT'换成table_name='表名’。
2.处理表空间的碎片问题
可以通过以下几种方式回收表的空闲空间。
第一种方式
alter table scott.emp move;
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'EMP')
- EMP换成需要回收的表名字
- 回收之后需要重新收集表的统计信息，统计信息的收集方式可以参考
- 统计信息收集完成之后，可以重启再重新检查一遍表碎片情况
第二种方式
alter table emp enable row movement;
alter table emp shrink space cascade;
alter table emp disable row movement;
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'EMP')
- EMP换成需要回收的表名字
- 回收之后需要重新收集表的统计信息，统计信息的收集方式可以参考
- 统计信息收集完成之后，可以重启再重新检查一遍表碎片情况
第三种方式
可以通过ctas的方式，重建碎片率高的表，如果创建过程中，源表有记录更改，那么数据可能存在不准确的情况
create table emp_temp as select * from emp;