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%以上了。