文章目录
参考:
How to find Objects Fragmented below High water mark (Doc ID 337651.1)
https://www.modb.pro/db/621044
一、查询高水位
有如下几种方法:
- 查询dba_tables(user_tables)
- dump SEGMENT HEADER block
alter system dump datafile 4 block 168;
select value from v$diag_info where name like ‘De%’; - Segment Advisor
- show_space(For 11gr1 and higher version)
1. 方法一:查询dba_tables(user_tables)
1.1 收集表的统计信息(非分区表)
ANALYZE TABLE HR.LAURATEST COMPUTE STATISTICS;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TRFFPN_APP',
tabname => 't1',
estimate_percent => 100,
method_opt => 'FOR ALL INDEXED COLUMNS',
CASCADE => TRUE,
no_invalidate => FALSE,
degree => 2);
END;
/
1.2 查询高水位
--表需要的存储空间:表的存储结构是:行×行数,如果知道了总共有多少行,每行的平均长度,两者相乘,再除于90%的使用率
--表实际存储的空间:数据实际存储在数据文件中是以块的形式存储的,每个数据文件8K,块的数量乘于8k
SELECT owner
, table_name 表名
, num_rows 表数据条数
, ROUND(avg_row_len*num_rows/1024/1024) 表需要存储空间MB
, ROUND(blocks*8/1024) 表实际存储空间MB
, ROUND((blocks*8/1024-avg_row_len*num_rows/1024/1024-blocks*8*0.1/1024)) 表可回收空间MB
FROM dba_tables
WHERE temporary = 'N'
--AND table_name IN ('LAURATEST')
AND owner NOT IN ('SYS','SYSTEM','DBSNMP','XDB','GSMADMIN_INTERNAL','WMSYS','SYSMAN','OUTLN', 'EXFSYS', 'APEX_030200', 'ORDDATA', 'MDSYS', 'APPQOSSYS', 'ORDSYS', 'FLOWS_FILES')
ORDER BY 6 DESC nulls LAST
;
SELECT owner,
table_name,
ROUND(blocks * 8 / 1024, 2) "High_Water(MB)",
ROUND(num_rows * avg_row_len / 1024 / 1024, 2) "Used_Space(MB)",
ROUND(blocks * 0.1 * 8 / 1024, 2) "Reserve_Space(MB)",
ROUND(blocks * 8 / 1024 - num_rows * avg_row_len / 1024 / 1024 - blocks * 8 * 0.1 / 1024, 2) "Recovery_Space(MB)",
--一般超过20%就需要回收了
DECODE(NVL((blocks * 8), 0), 0, 0, ROUND((1 - (num_rows * avg_row_len / 1024) / (blocks * 8 * 0.9)) * 100, 0)) "Percent(%)"
FROM dba_tables
WHERE temporary = 'N'
AND table_name IN ('LAURATEST')
ORDER BY 6 DESC;
二、回收高水位
1. shrink space(10g之后,需要表空间是基于自动段管理的)
--a) 整理表,不影响DML操作
alter table TABLE_NAME enable row movement; --开启行迁移
alter table TABLE_NAME shrink space compact; --(可以在压缩期间进行DML操作和查询),收缩表,不会降低hwm
--b)重置高水位,此时不能有DML操作
ALTER TABLE TABLE_NAME SHRINK SPACE; --(调整HWM时将阻塞DML操作),收缩表,并且降低hwm
ALTER TABLE TABLE_NAME DISABLE ROW MOVEMENT; --关闭行移动
segment shrink执行的两个阶段:
1. 数据重组(compact):
通过一系列insert、delete操作,将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁,即只在需要移动的行上加锁。
由于涉及到rowid的改变,需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。
2. HWM调整:第二阶段是调整HWM位置,释放空闲数据块。
此过程需要在表上加X锁,会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。
注意:shrink space语句两个阶段都执行。
shrink space compact只执行第一个阶段。
如果系统业务比较繁忙,
可以先执行shrink space compact重组数据,然后在业务不忙的时候再执行shrink space降低HWM释放空闲数据块。
由于是通过DML(delete和insert)操作进行的,会产生大量redo,注意archivelog目录的空间大小问题;同时undo表空间也会暴增
--新的收缩语句,待测试和确认???
alter table tmp3 parallel 4; --如果是大表的话,可以对表开启并行
alter table tmp3 enable row movement; --启动行迁移,shrink必须开启行迁移
alter table tmp3 shrink space check; -- 检查表能否被收缩 ORA-10655: Segment can be shrunk表示可以被收缩
--语法:alter table shrink space[|cpmpact|cascade];
alter table shrink space compcat; --把块中的数据堆到一起,但会保持high water mark,压缩阶段(compact),在业务高峰时可以先完成第一阶段.这个过程中会在只需要移动的行上加锁,对业务影响比较小
alter table shrink space; --收缩表,降低high water mark
alter table shrink space cascade; --收缩表,降低high water mark,并相关索引也要收缩。
alter index indexname shrink space; --收缩索引
alter table tmp3 disable row movement; --关闭行移动
alter table tmp3 noparallel; --如果之前开启了表的并行,结束后需要关闭
2. move table
ALTER TABLE table_name MOVE;
注意:
1. 会锁表
2. move是以block为单位重组数据,行的rowid都会跟着变化,索引会失效,需要重建索引。
3. 需要准备两倍的空间。
4. MOVE之后,HWM降低了,空闲块也上去了。
3. 表重建
如CATS(create table as select …) 或 复制要保留的数据到临时表T,DROP原表,然后RENAME临时表T为原表
4. 导出导入(exp/imp,expdp/impdp)
5. truncate (注意:此方法慎用)
6. DBMS_REDEFINITION表在线重定义
参考文档:
三、Others:查看数据所在数据文件和块号
select dbms_rowid.ROWID_RELATIVE_FNO(rowid) fno, dbms_rowid.rowid_block_number(rowid) block#, display
from HR.LAURATEST order by block#;