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 .00免费云主机域名0427246 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%以上了。

相关推荐: oracle参数open_cursors和session_cached_cursor详解！

1、open_cursors与session_cached_cursor的作用？ open_cursors设定每个session（会话）最多能同时打开多少个cursor（游标）。session_cached_cursor 设定每个session（会话）最多可以…