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)
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)
标签：name,NAME,sysaux,system,SYS,RUNNING,EXPORT,使用率,SCHEMA From： https://blog.51cto.com/u_13482808/6928408