ORACLE 02 高水位表

---

一、方法一

1. 查找高水位线的表

表需要的存储空间：表以数据块的形式存储在数据文件中，表的存储结构是：行×行数，如果知道了总共有多少行，每行的平均长度，两者相乘，再除于90%的使用率
表实际存储的空间：数据实际存储在数据文件中是以块的形式存储的，每个数据文件8K，块的数量乘于8k

SELECT owner
, TABLE_NAME 表名 
, NUM_ROWS 表实际数据条数
, ROUND(AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9) 表需要存储空间MB
, ROUND(BLOCKS*8/1024) 表实际存储空间MB
, ROUND((BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9)) RECOVER_MB
FROM dba_tables
--WHERE tablespace_name = 'TBS_ODS' 
--AND BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9 > 100
--AND rownum < 11 
WHERE --table_name in ('DATA_PMRECALC_TRX_SPC','MODEL_PMRECALC_MST_SPC')
owner not in ('SYS','SYSTEM','DBSNMP','XDB','GSMADMIN_INTERNAL','WMSYS','SYSMAN','OUTLN', 'EXFSYS', 'APEX_030200', 'ORDDATA'
, 'MDSYS', 'APPQOSSYS', 'ORDSYS', 'FLOWS_FILES')
order by RECOVER_MB desc
;

SELECT table_name,
ROUND ( (blocks * 8) / 1024, 2) "High_Water(MB)",
ROUND ( (num_rows * avg_row_len / 1024) / 1024, 2) "Used_Space(MB)",
ROUND ( (blocks * 10 / 100) * 8 / 1024, 2) "Reserve_Space(MB)",
ROUND ( (blocks * 8 - (num_rows * avg_row_len / 1024) - blocks * 8 * 10 / 100) /1024 , 2) "Recovery_Space(MB)"
, DECODE (NVL((blocks * 8),0), 0, 0, ROUND ( (1 - (num_rows * avg_row_len / 1024)/(blocks * 8)) * 100 ,0)) "Percent(%)" --一般超过20%就需要回收了
FROM user_tables
WHERE table_name = 'LOT_STEP_HISTORY_L'
ORDER BY 5 DESC;

2. 高水位回收

shrink space时会锁表，move 和 rebuild index

--a) 启动行迁移：
alter table LOT_FINISHED_HIST enable row movement ;
--b）进行表的收缩：
alter table LOT_FINISHED_HIST shrink space ;
--c）关闭行迁移
alter table LOT_FINISHED_HIST enable row movement ;

二、方法二

blocks字段和有数据的字段对比，用1减，求出没有数据的块的百分比（一般超过20%就需要回收了）

select round((1-a.used/b.num_total)*100,0)  percent from 
(SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) Used FROM P1ODSDA1.LOT_STEP_HISTORY_W) a,
(select blocks num_total from dba_tables where table_name='LOT_STEP_HISTORY_W' and owner='P1ODSDA1') b;

三、方法三

SELECT OWNER,
SEGMENT_NAME TABLE_NAME,
SEGMENT_TYPE,
GREATEST (ROUND ( 100 * ( NVL (HWM - AVG_USED_BLOCKS, 0 ) /GREATEST (NVL (HWM, 1), 1 )),2 ),0 ) WASTE_PER --没有数据的块的百分比
FROM ( 
SELECT A.OWNER OWNER,
A.SEGMENT_NAME,
A.SEGMENT_TYPE,
B.LAST_ANALYZED,
A.BYTES,
B.NUM_ROWS,
A.BLOCKS BLOCKS,
B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE (ROUND ((B.AVG_ROW_LEN * NUM_ROWS *( 1 + (PCT_FREE / 100 ))) / C.BLOCKSIZE,0 ),0 ,1 ,ROUND ((B.AVG_ROW_LEN * NUM_ROWS *( 1 + (PCT_FREE / 100 ))) / C.BLOCKSIZE,0 )) + 2 AVG_USED_BLOCKS,
ROUND (100 *( NVL (B.CHAIN_CNT, 0 ) / GREATEST( NVL (B.NUM_ROWS, 1 ), 1 )), 2 ) CHAIN_PER,
B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B, SYS.TS$ C
WHERE A.OWNER = B.OWNER
and SEGMENT_NAME = TABLE_NAME
and SEGMENT_TYPE = 'TABLE'
AND B.TABLESPACE_NAME = C.NAME
AND A.OWNER = 'SPC_TMP_DAT'
)
WHERE 1 = 1
--AND GREATEST (ROUND ( 100 * ( NVL (HWM - AVG_USED_BLOCKS, 0 ) /GREATEST (NVL (HWM, 1), 1 )),2 ),0 ) > 50
AND BLOCKS > 1000
ORDER BY WASTE_PER DESC 
;