sysaux表空间使用率高
查看表空间使用率
set linesize 200
set tab off
SELECT a.tablespace_name,
TRUNC(tablespace_size * b.block_size / 1024 / 1024) "Total_space(MB)",
TRUNC(used_space * b.block_size / 1024 / 1024) "Used_space(MB)",
TRUNC((TABLESPACE_SIZE - used_space) * b.block_size / 1024 / 1024) "Free_space(MB)",
USED_PERCENT "Used_percent(%)"
FROM DBA_TABLESPACE_USAGE_METRICS a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY USED_PERCENT desc;
TABLESPACE_NAME Total_space(MB) Used_space(MB) Free_space(MB) Used_percent(%)
------------------------------ --------------- -------------- -------------- ---------------
SYSAUX 3264 29493 1331 96.0031495
SYSTEM 32767 3220 29547 9.82685558
。。。
12 rows selected.
查看都是哪些对象占用了sysaux表空间
col Item for a30
col Schema for a20
set lines 200
SELECT occupant_name"Item",
round(space_usage_kbytes/1024/1024,3)"Space Used (GB)",
schema_name "Schema",
move_procedure "MoveProcedure"
FROM v$sysaux_occupants
ORDER BY 2 Desc;
Item Space Used (GB) Schema MoveProcedure
------------------------------ --------------- -------------------- ----------------------------------------------------------------
SM/AWR 28.036 SYS
SM/OPTSTAT .132 SYS
SM/ADVISOR .125 SYS
XDB .123 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE
EM .067 SYSMAN emd_maintenance.move_em_tblspc
SDO .065 MDSYS MDSYS.MOVE_SDO
JOB_SCHEDULER .05 SYS
AO .036 SYS DBMS_AW.MOVE_AWMETA
XSOQHIST .036 SYS DBMS_XSOQ.OlapiMoveProc
LOGMNR .013 SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE
ORDIM/ORDDATA .013 ORDDATA ordsys.ord_admin.move_ordim_tblspc
可以看到sm/awr组件占用最多
查找到那些占用sysaux表空间的基表，按照大小进行排序
select * from (select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024
from dba_segments where tablespace_name='SYSAUX' order by 4 desc) where rownum<=10;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024
------------------------------ ------------------------------ ------------------ ---------------
WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT__2369807153_0 INDEX PARTITION 3596
WRH$_EVENT_HISTOGRAM WRH$_EVENT__2369807153_0 TABLE PARTITION 3456
WRH$_LATCH WRH$_LATCH_2369807153_0 TABLE PARTITION 2304
WRH$_SQLSTAT WRH$_SQLSTA_2369807153_0 TABLE PARTITION 1957
WRH$_LATCH_MISSES_SUMMARY WRH$_LATCH__2369807153_0 TABLE PARTITION 1711
WRH$_LATCH_MISSES_SUMMARY_PK WRH$_LATCH__2369807153_0 INDEX PARTITION 1600
WRH$_SYSSTAT_PK WRH$_SYSSTA_2369807153_0 INDEX PARTITION 1600
WRH$_SYSSTAT WRH$_SYSSTA_2369807153_0 TABLE PARTITION 1536
WRH$_PARAMETER_PK WRH$_PARAME_2369807153_0 INDEX PARTITION 1408
WRH$_LATCH_PK WRH$_LATCH_2369807153_0 INDEX PARTITION 1344
10 rows selected.
显示当前的最小和最大SNAP_ID存在于DBA_HIST_SNAPSHOT:
SQL> SELECT MIN(SNAP_ID), MAX(SNAP_ID), COUNT(*) FROM SYS.DBA_HIST_SNAPSHOT;
MIN(SNAP_ID) MAX(SNAP_ID) COUNT(*)
------------ ------------ ----------
72532 72738 207
手动清理基表
步骤1：
在清理之前，使用以下命令暂时关闭awr快照收集。
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 0);
步骤2：
检查要保留的与当前SNAP_ID匹配的行数；
SELECT COUNT(*) FROM SYS.WRH$_SYSMETRIC_HISTORY
WHERE (DBID,SNAP_ID) IN (SELECT DBID,SNAP_ID FROM DBA_HIST_SNAPSHOT);
创建一个备份表来存储与当前快照id匹配的所有行
CREATE TABLE SYS.WRH$_EVENT_HISTOGRAM_PK_BACKUP TABLESPACE SYSAUX AS
SELECT * FROM SYS.WRH$_EVENT_HISTOGRAM_PK WHERE (DBID,SNAP_ID) IN (SELECT DBID,SNAP_ID FROM DBA_HIST_SNAPSHOT);
检查备份表中的行数是否与“步骤3”中的第一个脚本输出匹配
SELECT COUNT(*) FROM SYS.WRH$_EVENT_HISTOGRAM_PK_BACKUP;
步骤3：
truncate主表，例如:SYS.WRH$_EVENT_HISTOGRAM_PK和INSERT(使用直接路径)从备份表中保留的行，提交:
TRUNCATE TABLE SYS.WRH$_EVENT_HISTOGRAM_PK;
INSERT /*+ APPEND */ INTO SYS.WRH$_EVENT_HISTOGRAM_PK SELECT * FROM SYS.WRH$_EVENT_HISTOGRAM_PK_BACKUP;
COMMIT;
步骤4：
恢复awr快照收集
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 60);
步骤5：
删除备份表
DROP TABLE SYS.WRH$_EVENT_HISTOGRAM_PK_BACKUP;SM/AWR组件占用最多，基表数据需要清理。
参考文档
How to Manually Purge Orphan Rows from AWR Repository Tables In Sysaux Tablespace (Doc ID 2536631.1)
补充：
一.sysaux清理
在运维过程中经常会遇到sysaux表空间使用率过高的问题，SYSAUX表空间满了很大的一部分原因是AWR报告造成的，AWR的快照由一个MMON的后台进程及其从进程自动地每小时采集一次。为了节省空间，在Oracle 10g中快照会保留7天，11g的快照保留8天，超过的会自动删除，快照频率和保留时间都可以由用户修改
1.将历史统计信息保留时间设为无限
exec dbms_stats.alter_stats_history_retention(-1);2.truncate较大的TABLE
truncate table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY;
truncate table sys.WRI$_OPTSTAT_HISTGRM_HISTORY;3.清理历史统计信息
exec dbms_stats.purge_stats(sysdate-101);
exec dbms_stats.purge_stats(sysdate-51);
exec dbms_stats.purge_stats(sysdate-5);4.将历史统计信息保留时间设为10天
select dbms_stats.get_stats_history_retention from dual;
exec dbms_stats.alter_stats_history_retention(10);6.对MOVE表的统计信息进行收集
EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTHEAD_HISTORY',cascade => TRUE);
EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTGRM_HISTORY',cascade => TRUE);
EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_IND_HISTORY',cascade => TRUE);
EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_TAB_HISTORY',cascade => TRUE);
EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_OPR',cascade => TRUE);
EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_AUX_HISTORY',cascade => TRUE);二.清理SYAUX下的无效ASH信息
1.检查是否有无效的ASH信息
select count(*)
from sys.wrh$_active_session_history a
where not exists (select 1
from sys.wrm$_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number);2.清理无效的ASH信息
delete
from sys.wrh$_active_session_history a
where not exists (select 1
from sys.wrm$_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number);3.对ASH表清理后的碎片整理
alter table sys.wrh$_active_session_history enable row movement;
alter table sys.wrh$_active_session_history shrink space cascade;
alter table sys.wrh$_active_session_history disable row movement;4.收集碎片整理后表的统计信息
EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRH$_ACTIVE_SESSION_HISTORY',cascade => TRUE);3.检查表空间可收缩的的位置
select a.FILE#,a.NAME,
a.BYTES / 1024 / 1024 mb,
ceil(HWM * A.BLOCK_SIZE) / 1024 / 1024 RESIZETO,
'ALTER DATABASE DATAFILE "' ||
A.NAME || "' RESIZE ' ||
(trunc(CEIL(HWM * A.BLOCK_SIZE) / 1024 / 1024)+20) || 'M;' RESIZECMD
from v$datafile a,
(SELECT C.file_id, MAX(C.block_id + C.blocks – 1) HWM
FROM DBA_EXTENTS C
GROUP BY FILE_ID) B
WHERE A.FILE# = B.FILE_ID
AND a.tablespace='SYSAUX'
ORDER BY 5;三.SYSAUX清理后的检查
1.清理后的无效INDEX检查
select * from dba_indexes where status<>‘VALID’ AND STATUS<>‘N/A’;
SELECT * FROM DBA_IND_PARTITIONS WHERE STATUS<>‘USABLE’ AND STATUS<>‘N/A’;sysaux空间清理WRH$_SQLTEX
通过查询发现SYS_LOB0000006409C00004$$ 这个segment 占用了40多个G，通过查询该segment属于WRH$_SQLTEXT
WRH$表里面存储的是关于AWR数据，大部分WRH$表是分区表当AWR快照在保留期过后被删除或自动清除时，这些表中的数据将通过删除整个分区来删除。删除分区还会删除属于分区的整个LOB段。
但是WRH$_SQLTEXT不是分区表，当删除或清除AWR快照时，未分区的行将被删除。因此，这些表上的任何LOB段都不会被删除，只是从它们中删除LOB数据。
这意味着分配给这类LOB段的存储将永远不会被释放，并且它们将保持分配的最大区段数量，即使存储在其中的大部分数据已经被删除。
通过查询产生的AWR快照可以正常删除且表WRH$_SQLTEXT行数只有4W多条证明AWR的自动清理机制是正常的。由于WRH$_SQLTEXT表的删除操作是delete所有不释放空间。
如果想回收空间，也可以采用下列方案：
方案一：
避免在重建过程中AWR操作的干扰。将数据库启动到restrict阶段
然后执行
alter table wrh$_sqltext move lob(sql_text) store as SYS_LOB0000006409C00004$$ tablespace sysaux;
查询失效索引并重建
select index_name from dba_indexes where table_name='WRH$_SQLTEXT' and status='UNUSABLE';
alter index WRH$_SQLTEXT_PK rebuild;
然后正常启动数据库
这一方案需要停止业务，且需要一定的空闲空间。
方案二：
alter table WRH$_SQLTEXT enable row movement;
alter table WRH$_SQLTEXT shrink space cascade;
alter table WRH$_SQLTEXT disable row movement;
执行shrink space命令的过程中，会对表上锁。但不需要额外空闲空间。
方案三：
Truncate table WRH$_SQLTEXT;
直接将表截断，速度最快，但存储的awr存储的sql语句信息将会被删除。
参考文档
High Storage Consumption for LOBs in SYSAUX Tablespace (Doc ID 396502.1)
system表空间使用率高
数据库 System 表空间使用率过高，System 的空间使用对于Oracle数据库的健康运行具备深远影响，需要认真关注和分析：
SYS> SELECT distinct
2 to_char(sysdate,'YYYY-MM-DD HH24:Mi') as COLLECT_DATE,
3 T.TABLESPACE_NAME as
4 tablespace_name,
5 trunc(T.TABLESPACE_SIZE * 8 / 1024,2) as TOTAL_M,
6 trunc((T.TABLESPACE_SIZE-T.USED_SPACE)*8/1024,2)as REMAIN_M, trunc(T.USED_PERCENT,2) as
7 USE_RATIO
8 FROM
9 DBA_TABLESPACE_USAGE_METRICS t
10 ORDER BY USE_RATIO desc;
COLLECT_DATE TABLESPACE_NAME TOTAL_M REMAIN_M USE_RATIO
---------------- ------------------------------ ---------- ---------- ----------
2023-04-03 12:19 SYSTEM 28668 4876.4 82.99
...问题原因
查看 System 使用空间较多的对象，发现大量 SYS_EXPORT_SCHEMA 开头的表，怀疑是expdp job异常中断产生的。
SYS> select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments
2 where tablespace_name='SYSTEM' group by segment_name)
3 where sx>100 order by sx desc;
SEGMENT_NAME SX
--------------------------------------------------------------------------------- ----------
FGA_LOG$ 13813
SYS_LOB0030892180C00045$$ 3396
SYS_LOB0030871501C00045$$ 3392
SYS_LOB0030894763C00045$$ 3392
...
SYS> select owner,
2 table_name,
3 column_name,
4 segment_name,
5 index_name
6 from dba_lobs where segment_name='SYS_LOB0030892180C00045$$';
OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------------
SEGMENT_NAME INDEX_NAME
------------------------------ ------------------------------
SYS SYS_EXPORT_SCHEMA_07
XML_CLOB
SYS_LOB0030892180C00045$$ SYS_IL0030892180C00045$$
SYS> select owner,
2 table_name,
3 column_name,
4 segment_name,
5 index_name
6 from dba_lobs where segment_name='SYS_LOB0030871501C00045$$';
OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------------
SEGMENT_NAME INDEX_NAME
------------------------------ ------------------------------
SYS SYS_EXPORT_SCHEMA_06
XML_CLOB
SYS_LOB0030871501C00045$$ SYS_IL0030871501C00045$$查看发现确实是有部分job中断，状态都是“NOT RUNNING”。
SYS>SET lines 200
SYS>COL owner_name FORMAT a10;
SYS>COL job_name FORMAT a20
SYS>COL state FORMAT a12
SYS>COL operation LIKE state
SYS>COL job_mode LIKE state
SYS>COL owner.object for a50
SYS>-- locate Data Pump jobs:
SYS>SELECT owner_name, job_name, rtrim(operation) "OPERATION",
2 rtrim(job_mode) "JOB_MODE", state, attached_sessions
3 FROM dba_datapump_jobs
4 WHERE job_name NOT LIKE 'BIN$%'
5 ORDER BY 1,2;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ------------ ------------ ------------ -----------------
U_XXX SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA NOT RUNNING 0
U_XXX SYS_EXPORT_SCHEMA_02 EXPORT SCHEMA NOT RUNNING 0
U_XXX SYS_EXPORT_SCHEMA_03 EXPORT SCHEMA NOT RUNNING 0
U_XXX SYS_EXPORT_SCHEMA_04 EXPORT SCHEMA NOT RUNNING 0
U_XXX SYS_EXPORT_SCHEMA_05 EXPORT SCHEMA NOT RUNNING 0
U_XXX SYS_EXPORT_SCHEMA_06 EXPORT SCHEMA NOT RUNNING 0
SYS SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA NOT RUNNING 0
SYS SYS_EXPORT_SCHEMA_02 EXPORT SCHEMA NOT RUNNING 0
SYS SYS_EXPORT_SCHEMA_03 EXPORT SCHEMA NOT RUNNING 0
SYS SYS_EXPORT_SCHEMA_04 EXPORT SCHEMA NOT RUNNING 0
SYS SYS_EXPORT_SCHEMA_05 EXPORT SCHEMA NOT RUNNING 0
SYS SYS_EXPORT_SCHEMA_06 EXPORT SCHEMA NOT RUNNING 0
SYS SYS_EXPORT_SCHEMA_07 EXPORT SCHEMA NOT RUNNING 0
SYS SYS_EXPORT_SCHEMA_08 EXPORT SCHEMA NOT RUNNING 0
SYS SYS_EXPORT_TABLE_01 EXPORT TABLE NOT RUNNING 0
SYS SYS_EXPORT_TABLE_02 EXPORT TABLE NOT RUNNING 0
SYS SYS_EXPORT_TABLE_03 EXPORT TABLE NOT RUNNING 0
17 rows selected.查看是否有DataPump产生的外部表
SYS>set linesize 200 trimspool on
SYS>set pagesize 2000
SYS>col owner form a30
SYS>col created form a25
SYS>col last_ddl_time form a25
SYS>col object_name form a30
SYS>col object_type form a25
SYS>select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
2 to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created ,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
3 from dba_objects
4 where object_name like 'ET$%'
5 /
no rows selected
SYS>select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
2 from dba_external_tables
3 order by 1,2
4 /
no rows selected解决方案
删除DataPump中断产生的SYS_EXPORT_XXX表。
SYS>SELECT 'DROP TABLE '||o.owner||'.'||object_name||' PURGE;'
2 FROM dba_objects o, dba_datapump_jobs j
3 WHERE o.owner=j.owner_name AND o.object_name=j.job_name
4 AND j.job_name NOT LIKE 'BIN$%';
'DROPTABLE'||O.OWNER||'.'||OBJECT_NAME||'PURGE;'
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE U_XXX.SYS_EXPORT_SCHEMA_01 PURGE;
DROP TABLE U_XXX.SYS_EXPORT_SCHEMA_06 PURGE;
...
SYS>DROP TABLE U_XXX.SYS_EXPORT_SCHEMA_01 PURGE;
DROP TABLE U_XXX.SYS_EXPORT_SCHEMA_06 PURGE;
...
Table dropped.
SYS>
Table dropped.参考文档
How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1)
