ITPub博客

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

原创 Oracle 作者:始于脚下 时间:2018-03-26 17:40:02 0 删除 编辑

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.

可以看到itemSYSAUX的条目占了将近所有单个数据文件的空间,通过查找资料,这些数据是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%以上了。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31403259/viewspace-2152235/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
拥有多年电信行业Oracle数据库管理经验,拥有Oracle OCP、OCM证书,擅长各个场景下的数据库备份与恢复、性能分析管理、上线规划及部署实施、架构设计、自动化运维等。

注册时间:2016-10-18

  • 博文量
    59
  • 访问量
    50064