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

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

告警日志内容：

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  

Item    Space Used (GB) Schema     Move Procedure

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

AUDSYS  31.487793 AUDSYS

SDO .075866699 MDSYS     MDSYS.MOVE_SDO

XDB .065368652 XDB     XDB.DBMS_XDB.MOVEXDB_TABLESPACE

SM/OTHER    .046875 SYS

XSOQHIST .036743164 SYS     DBMS_XSOQ.OlapiMoveProc

AO .036743164 SYS     DBMS_AW.MOVE_AWMETA

SM/OPTSTAT .023986816 SYS

ORDIM/ORDDATA .015686035 ORDDATA     ordsys.ord_admin.move_ordim_tblspc

JOB_SCHEDULER .009094238 SYS

WM .007019043 WMSYS     DBMS_WM.move_proc

SMON_SCN_TIME .006225586 SYS

Item    Space Used (GB) Schema     Move Procedure

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

TEXT .003601074 CTXSYS     DRI_MOVE_CTXSYS

SM/ADVISOR .002624512 SYS

SQL_MANAGEMENT_BASE .000854492 SYS

PL/SCOPE .000488281 SYS

ORDIM .000427246 ORDSYS     ordsys.ord_admin.move_ordim_tblspc

SM/AWR .000366211 SYS

AUTO_TASK .000305176 SYS

STREAMS .000244141 SYS

EM_MONITORING_USER .000183105 DBSNMP

LOGSTDBY  .00012207 SYSTEM     SYS.DBMS_LOGSTDBY.SET_TABLESPACE

ORDIM/SI_INFORMTN_SCHEMA  0 SI_INFORMTN_SCHEMA     ordsys.ord_admin.move_ordim_tblspc

Item    Space Used (GB) Schema     Move Procedure

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

EM  0 SYSMAN     emd_maintenance.move_em_tblspc

STATSPACK  0 PERFSTAT

ULTRASEARCH  0 WKSYS     MOVE_WK

ULTRASEARCH_DEMO_USER  0 WK_TEST     MOVE_WK

ORDIM/ORDPLUGINS  0 ORDPLUGINS     ordsys.ord_admin.move_ordim_tblspc

XSAMD  0 OLAPSYS     DBMS_AMD.Move_OLAP_Catalog

TSM  0 TSMSYS

AUDIT_TABLES  0 SYS     DBMS_AUDIT_MGMT.move_dbaudit_tables

LOGMNR  0 SYSTEM     SYS.DBMS_LOGMNR_D.SET_TABLESPACE

EXPRESSION_FILTER  0 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  

Item    Space Used (GB) Schema     Move Procedure

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

SDO .075866699 MDSYS     MDSYS.MOVE_SDO

XDB .065368652 XDB     XDB.DBMS_XDB.MOVEXDB_TABLESPACE

SM/OTHER    .046875 SYS

XSOQHIST .036743164 SYS     DBMS_XSOQ.OlapiMoveProc

AO .036743164 SYS     DBMS_AW.MOVE_AWMETA

SM/OPTSTAT .023986816 SYS

ORDIM/ORDDATA .015686035 ORDDATA     ordsys.ord_admin.move_ordim_tblspc

JOB_SCHEDULER .009094238 SYS

WM .007019043 WMSYS     DBMS_WM.move_proc

SMON_SCN_TIME .006225586 SYS

TEXT .003601074 CTXSYS     DRI_MOVE_CTXSYS

Item    Space Used (GB) Schema     Move Procedure

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

SM/ADVISOR .002624512 SYS

AUDSYS .002563477 AUDSYS

SQL_MANAGEMENT_BASE .000854492 SYS

PL/SCOPE .000488281 SYS

ORDIM .000427246 ORDSYS     ordsys.ord_admin.move_ordim_tblspc

SM/AWR .000366211 SYS

AUTO_TASK .000305176 SYS

STREAMS .000244141 SYS

EM_MONITORING_USER .000183105 DBSNMP

LOGSTDBY  .00012207 SYSTEM     SYS.DBMS_LOGSTDBY.SET_TABLESPACE

ORDIM/SI_INFORMTN_SCHEMA  0 SI_INFORMTN_SCHEMA     ordsys.ord_admin.move_ordim_tblspc

Item    Space Used (GB) Schema     Move Procedure

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

EM  0 SYSMAN     emd_maintenance.move_em_tblspc

STATSPACK  0 PERFSTAT

ULTRASEARCH  0 WKSYS     MOVE_WK

ULTRASEARCH_DEMO_USER  0 WK_TEST     MOVE_WK

ORDIM/ORDPLUGINS  0 ORDPLUGINS     ordsys.ord_admin.move_ordim_tblspc

XSAMD  0 OLAPSYS     DBMS_AMD.Move_OLAP_Catalog

TSM  0 TSMSYS

AUDIT_TABLES  0 SYS     DBMS_AUDIT_MGMT.move_dbaudit_tables

LOGMNR  0 SYSTEM     SYS.DBMS_LOGMNR_D.SET_TABLESPACE

EXPRESSION_FILTER  0 EXFSYS

32 rows selected.

SQL>

可以看到sysaux条目占用的空间已经全部释放，告警日志也不再提示 SYSAUX表空间无法扩展的问题，查询sysaux表空间使用率，也已经空闲95%以上了。