Shulou(Shulou.com)06/01 Report--

Oracle 12c sysaux table space insufficient handling-AUDSYS.CLI_SWP$def5007c$1 $1

Alarm log content:

ORA-1688: unable to extend table AUDSYS.CLI_SWP$def5007c$1 $1 partition HIGH_PART by 128 in tablespace SYSAUX [TEST]

Switch from the alarm message to the corresponding pdb to view the sysaux tablespace usage: the sysaux tablespace utilization has reached 100%. The analysis process and solutions are as follows:

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.

You can see that the entries whose item is SYSAUX take up nearly all the space of a single data file. By searching for information, these data are audit data stored in Unified Audit, a new feature of Oracle 12c, which can be cleaned directly in the following ways, or you can refer to official documents and clean up in other ways. The connection is as follows:

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 >

You can see that all the space occupied by sysaux entries has been released, the alarm log no longer indicates that the SYSAUX table space cannot be expanded, and the query sysaux table space utilization is more than 95% free.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.