【Oracle SQL】高水位表

---

参考：
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#;