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（会话）最多可以…
免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。
微信扫一扫 
