Oracle 12c sysaux 表空间不足处理-AUDSYS.CLI_SWP$def5007c$1$1

2025-04-13 技术教程

告警日志内容：

ORA-1688: unable to extend table AUDSYS.CLI_SWP$def5007c$1$1 partition HIGH_PART by 128 in tablespace SYSAUX [TEST]

从告警信息直接切换到对应的pdb下查看sysaux表空间空间占用情况：sysaux表空间使用率已经100%，分析过程及解决办法如下：

SQL> alter session set container=TEST;

Session altered.

SQL> SELECT occupant_name "Item",

space_usage_kbytes / 1048576 "Space Used (GB)",

schema_name "Schema",

move_procedure "Move Procedure"

FROM v$sysaux_occupants

ORDER BY 2 desc; 2 3 4 5 6

ItemSpace Used (GB) SchemaMove Procedure

-------------------------------------------------- --------------- ---------------------------------------- --------------------------------------------------

AUDSYS31.487793 AUDSYS

SDO.075866699 MDSYSMDSYS.MOVE_SDO

XDB.065368652 XDBXDB.DBMS_XDB.MOVEXDB_TABLESPACE

SM/OTHER.046875 SYS

XSOQHIST.036743164 SYSDBMS_XSOQ.OlapiMoveProc

AO.036743164 SYSDBMS_AW.MOVE_AWMETA

SM/OPTSTAT.023986816 SYS

ORDIM/ORDDATA.015686035 ORDDATAordsys.ord_admin.move_ordim_tblspc

JOB_SCHEDULER.009094238 SYS

WM.007019043 WMSYSDBMS_WM.move_proc

SMON_SCN_TIME.006225586 SYS

ItemSpace Used (GB) SchemaMove Procedure

-------------------------------------------------- --------------- ---------------------------------------- --------------------------------------------------

TEXT.003601074 CTXSYSDRI_MOVE_CTXSYS

SM/ADVISOR.002624512 SYS

SQL_MANAGEMENT_BASE.000854492 SYS

PL/SCOPE.000488281 SYS

ORDIM.000427246 ORDSYSordsys.ord_admin.move_ordim_tblspc

SM/AWR.000366211 SYS

AUTO_TASK.000305176 SYS

STREAMS .000244141 SYS

EM_MONITORING_USER.000183105 DBSNMP

LOGSTDBY.00012207 SYSTEMSYS.DBMS_LOGSTDBY.SET_TABLESPACE

ORDIM/SI_INFORMTN_SCHEMA0 SI_INFORMTN_SCHEMAordsys.ord_admin.move_ordim_tblspc

ItemSpace Used (GB) SchemaMove Procedure

-------------------------------------------------- --------------- ---------------------------------------- --------------------------------------------------

EM0 SYSMANemd_maintenance.move_em_tblspc

STATSPACK0 PERFSTAT

ULTRASEARCH0 WKSYSMOVE_WK

ULTRASEARCH_DEMO_USER0 WK_TESTMOVE_WK

ORDIM/ORDPLUGINS0 ORDPLUGINSordsys.ord_admin.move_ordim_tblspc

XSAMD0 OLAPSYSDBMS_AMD.Move_OLAP_Catalog

TSM0 TSMSYS

AUDIT_TABLES0 SYSDBMS_AUDIT_MGMT.move_dbaudit_tables

LOGMNR0 SYSTEMSYS.DBMS_LOGMNR_D.SET_TABLESPACE

EXPRESSION_FILTER0 EXFSYS

32 rows selected.

可以看到item为SYSAUX的条目占了将近所有单个数据文件的空间，通过查找资料，这些数据是Oracle 12c的新特性Unified Audit存放的审计数据，可以通过以下方式直接清理，也可以参考官方文档，用其他方式进行清理，连接如下：

https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_audit_mgmt.htm#ARPLS241

SQL> begin

dbms_audit_mgmt.clean_audit_trail(

audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

use_last_arch_timestamp => FALSE);

end;

/ 2 3 4 5 6

PL/SQL procedure successfully completed.

SQL> SELECT occupant_name "Item",

space_usage_kbytes / 1048576 "Space Used (GB)",

schema_name "Schema",

move_procedure "Move Procedure"

FROM v$sysaux_occupants

ORDER BY 2 desc; 2 3 4 5 6

ItemSpace Used (GB) SchemaMove Procedure

-------------------------------------------------- --------------- ---------------------------------------- --------------------------------------------------

SDO.075866699 MDSYSMDSYS.MOVE_SDO

XDB.065368652 XDBXDB.DBMS_XDB.MOVEXDB_TABLESPACE

SM/OTHER.046875 SYS

XSOQHIST.036743164 SYSDBMS_XSOQ.OlapiMoveProc

AO.036743164 SYSDBMS_AW.MOVE_AWMETA

SM/OPTSTAT.023986816 SYS

ORDIM/ORDDATA.015686035 ORDDATAordsys.ord_admin.move_ordim_tblspc

JOB_SCHEDULER.009094238 SYS

WM.007019043 WMSYSDBMS_WM.move_proc

SMON_SCN_TIME.006225586 SYS

TEXT.003601074 CTXSYSDRI_MOVE_CTXSYS

ItemSpace Used (GB) SchemaMove Procedure

-------------------------------------------------- --------------- ---------------------------------------- --------------------------------------------------

SM/ADVISOR.002624512 SYS

AUDSYS.002563477 AUDSYS

SQL_MANAGEMENT_BASE.000854492 SYS

PL/SCOPE.000488281 SYS

ORDIM.000427246 ORDSYSordsys.ord_admin.move_ordim_tblspc

SM/AWR.000366211 SYS

AUTO_TASK.000305176 SYS

STREAMS .000244141 SYS

EM_MONITORING_USER.000183105 DBSNMP

LOGSTDBY.00012207 SYSTEMSYS.DBMS_LOGSTDBY.SET_TABLESPACE

ORDIM/SI_INFORMTN_SCHEMA0 SI_INFORMTN_SCHEMAordsys.ord_admin.move_ordim_tblspc

ItemSpace Used (GB) SchemaMove Procedure

-------------------------------------------------- --------------- ---------------------------------------- --------------------------------------------------

EM0 SYSMANemd_maintenance.move_em_tblspc

STATSPACK0 PERFSTAT

ULTRASEARCH0 WKSYSMOVE_WK

ULTRASEARCH_DEMO_USER0 WK_TESTMOVE_WK

ORDIM/ORDPLUGINS0 ORDPLUGINSordsys.ord_admin.move_ordim_tblspc

XSAMD0 OLAPSYSDBMS_AMD.Move_OLAP_Catalog

TSM0 TSMSYS

AUDIT_TABLES0 SYSDBMS_AUDIT_MGMT.move_dbaudit_tables

LOGMNR0 SYSTEMSYS.DBMS_LOGMNR_D.SET_TABLESPACE

EXPRESSION_FILTER0 EXFSYS

32 rows selected.

SQL>