******************************************************************************************************
只收缩
收缩(降低)水位线
-
ALTER TABLE t ENABLE ROW MOVEMENT;
-
ALTER TABLE t SHRINK SPACE;
******************************************************************************************************
收缩并验证
------ 收缩水位 并验证的效果
---打开采集开关
ALTER SESSION SET statistics_level = all;
---正常运行
SELECT * FROM IERP_ERROR_TRACKING_INFO;
SELECT count(*) FROM IERP_ERROR_TRACKING_INFO --5173
---查看采集日志
select *
from v$sql sqls
where sqls.SQL_TEXT like '%SELECT count(*) FROM IERP_ERROR_TRACKING_INFO%'
---查看采集日志详情
select last_output_rows, last_cr_buffer_gets, last_cu_buffer_gets
from v$sql_plan_statistics stat
where stat.SQL_ID = 'f6fcqk8k1tgyq' --7688
---收缩水位
ALTER TABLE IERP_ERROR_TRACKING_INFO ENABLE ROW MOVEMENT;
ALTER TABLE IERP_ERROR_TRACKING_INFO SHRINK SPACE;
--收缩后,再从上到下跑一次,看看last_cr_buffer_gets读取的个数,是不是少了很多
******************************************************************************************************
表空间空间查看
SELECT Upper(F.TABLESPACE_NAME) "table name",
D.TOT_GROOTTE_MB "totale size(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "used size(M)",
To_char(Round((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "used percent",
F.TOTAL_BYTES "free size (M)",
F.MAX_BYTES "max size(M)"
FROM(SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
Round(Max(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1