客户吐槽数仓太大，直接30T瘦身到10T，这下舒服了...

2024年的最后一次出差了，马上2025年了，加班到10点拖着疲惫的身体回到酒店，本来想睡觉的，突然想起明天的文章还要发哎，就想起了前不久客户一直抱怨的数据仓库太大了，给客户详细的做了一次瘦身，效果非常明显，在这里就总结出来分享给大家…

表空间太大了，数据量大，卡顿，执行计划总变，水位线不断上涨，尤其在数仓，给做一次瘦身是非常有必要的.

1.系统表空间清理

SYSAUX表空间被称为系统辅助表空间,主要的目的是为SYSTEM表空间减负。

1.1 表空间的使用率

比如大表大部分都是AUD$和WRH$开头的AWR基表，
AUD$使用SYSTEM表空间，
AWR的基表使用SYSAUX表空间。
SELECT df.tablespace_name, COUNT (*) datafile_count,
ROUND (SUM (df.BYTES) / 1048576) size_mb,
ROUND (SUM (free.BYTES) / 1048576, 2) free_mb,
ROUND (SUM (df.BYTES) / 1048576 - SUM (free.BYTES) / 1048576, 2 ) used_mb,
ROUND (MAX (free.maxbytes) / 1048576, 2) maxfree,
100 - ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_used,
ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_free
FROM dba_data_files df,
(SELECT tablespace_name, file_id, SUM (BYTES) BYTES, MAX (BYTES) maxbytes FROM dba_free_space GROUP BY tablespace_name, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+) 
AND df.file_id = free.file_id(+)
and df.tablespace_name IN ('SYSAUX','SYSTEM')
GROUP BY df.tablespace_name
使用SYSTEM和SYSAUX表空间的比较大的表有哪些呢？
select * from (
select segment_name,sum(bytes)/1024/1024 total_mb,tablespace_name from dba_segments where tablespace_name in 
('SYSTEM','SYSAUX') 
group by segment_name,tablespace_name order by 2 desc)
where rownum <=20;
SELECT occupant_name "Item", 
space_usage_kbytes / 1048576 "Space Used (GB)", 
schema_name "Schema", 
move_procedure "Move Procedure" 
FROM v$sysaux_occupants 
ORDER BY 1 ;

1.2 AUD$清理

先清理审计的数据，一般先迁移表空间即可，
如果空间不够，可以清空AUD$
--清空表
TRUNCATE TABLE SYS.AUD$ reuse storage;
--迁移审计表空间
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'TBS_AUDIT');
END;
/
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'TBS_AUDIT');
END;
/
SELECT table_name, tablespace_name 
FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') 
ORDER BY table_name;
select bytes/1024/1024 
from dba_segments 
where segment_name='AUD$';
--自动清理策略
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY
(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
audit_trail_property_value => 102400 /* 100MB*/ \
);
END;
/
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY
(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
audit_trail_property_value => 5 /* days */
);
END;
/

1.3 AWR的基表清理

SYSAUX表空间过多的大部分都是AWR的基表，这样只要删除部分AWR数据理论上就可以回收一部分SYSAUX表空间。

清理WRH$_ACTIVE_SESSION_HISTORY和
WRH$_EVENT_HISTOGRAM两张表
1.清理WRH$_ACTIVE_SESSION_HISTORY
通常AWR的数据都会设置保留期限
select * from dba_hist_wr_control;
select count(*) from  
WRH$_ACTIVE_SESSION_HISTORY;
AWR表未根据sys.wrm$_wr_control中的设置进行清除。
因此，表正在积累越来越多的行，与这些表关联的段变得非常大
见Doc ID 387914.1
使用DBMS_WORKLOAD_REPOSITORY包
清理过期或者不需要的AWR数据，可以回收这部分空间
select min(snap_id),max(snap_id) 
from  wrh$_active_session_history;
exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE 
(
low_snap_id =>1,
high_snap_id => 30000
);
--回收分区水位线
SELECT owner,
  segment_name,
  partition_name,
  segment_type,
  bytes/1024/1024/1024 Size_GB
FROM dba_segments
WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';
分区进行MOVE操作，来回收空间：
alter table WRH$_ACTIVE_SESSION_HISTORY 
move partition WRH$_ACTIVE_SESSION_HISTORY_3996260375_4090;
分区表进行MOVE之后，需要重建索引
select index_name 
from dba_indexes 
where table_name='WRH$_ACTIVE_SESSION_HISTORY';
select partition_name from dba_ind_partitions 
where index_name='WRH$_ACTIVE_SESSION_HISTORY_PK';
alter index WRH$_ACTIVE_SESSION_HISTORY_PK 
rebuild partition WRH$_ACTIVE_SESSION_HISTORY_3996260375_4090;
2.清理WRH$_EVENT_HISTOGRAM
select partition_name 
from dba_tab_partitions
where table_name='WRH$_EVENT_HISTOGRAM';
分区进行MOVE操作，来回收空间
alter table WRH$_EVENT_HISTOGRAM 
move partition WRH$_EVENT_HISTOGRAM_3996260375_4090;
分区表进行MOVE之后，需要重建索引
select index_name 
from dba_indexes 
where table_name='WRH$_EVENT_HISTOGRAM';
select partition_name from dba_ind_partitions 
where index_name='WRH$_EVENT_HISTOGRAM_PK;
alter index WRH$_EVENT_HISTOGRAM_PK 
rebuild partition WRH$_EVENT_HISTO_MXDB_MXSN;

1.4 统计信息保留时间

修改统计信息的保持时间，默认为31天，这里修改为7天，过期的统计信息会自动被删除

select 
dbms_stats.get_stats_history_retention 
from dual; 
exec 
dbms_stats.alter_stats_history_retention(7);

2. 降低水位线

水位线影响查询速度，因为这样的表本身比较大，索引也会很大，查询会很慢。消耗资源，因为表和索引都很大，查询时会消耗很多I/O资源，空间占用，所以要定期来降低水位线。

2.1 缩小数据文件

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
/
alter 
database
datafile '/oracle/app/oradata/ORCL/data01.dbf' 
resize 3800M;
如果低于HWM，则会出现以下报错
ORA-03297: file contains used data beyond requested RESIZE value

2.2 Shrink

1）Enable row movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT
2）Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE
3）Recover space, but don't amend the high water mark (HWM)
ALTER TABLE scott.emp SHRINK SPACE COMPACT
4）Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE
--收缩LOB
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE);
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE CASCADE);

2.3 LOB move表空间

将MOVE到相同的表空间
ALTER TABLE lob_tab MOVE LOB(data) 
STORE AS (TABLESPACE users);

3. 索引重建

3.1 分区及非分区重建索引

--分区
alter index xx.xxx rebuild partition xxx online parallel 4;
ALTER INDEX xx.xxx NOPARALLEL；
--非分区
alter index xx.xxx rebuild online parallel 4;
ALTER INDEX xx.xxx NOPARALLEL；

3.2 清理索引创建失败信息

declare
isClean boolean;
begin
isClean := FALSE;
while isClean=FALSE loop
isClean := dbms_repair.online_index_clean(dbms_repair.all_index_id,
dbms_repair.lock_wait);
dbms_lock.sleep(2);
end loop;
exception
when others then
RAISE;
end;
/

4.LOB字段

4.1 收缩

ALTER TABLE 
table_name MODIFY LOB(lob_column) (SHRINK SPACE);
ALTER TABLE table_name 
MODIFY LOB(lob_column) (SHRINK SPACE CASCADE);

4.2 移动表空间

ALTER TABLE 
lob_tab MOVE LOB(data) STORE AS (TABLESPACE users);复制

总结

Oracle数据库的“瘦身”通常指的是通过各种手段来减少数据库的存储占用，提高存储利用率，从而提升数据库的性能和稳定性。

在进行数据库瘦身之前，首先需要明确瘦身的目标和需求。这可能包括减少存储占用、提高查询性能、优化备份和恢复过程等。明确目标有助于制定合适的瘦身策略。