ORA-1688 错误SYSAUX

系统alert.log中有很多错误提示：
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_SESSION_HISTORY_3840252989_15027 by 128 in tablespace SYSAUX 
2021-08-11T16:18:45.388052+08:00
ORA-1652: unable to extend temp segment by 128 in tablespace SYSAUX 
2021-08-11T16:19:45.498345+08:00
ORA-1652: unable to extend temp segment by 128 in tablespace SYSAUX 

百度一搜，告诉可能时开启审计的原因，于是按照这个思路处理。
SQL> SET SERVEROUTPUT ON
BEGIN
IF sys.DBMS_AUDIT_MGMT.is_cleanup_initialized(sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
     DBMS_OUTPUT.put_line('YES');
   ELSE
     DBMS_OUTPUT.put_line('NO');
   END IF;
  END;
/SQL>
NO

PL/SQL procedure successfully completed.

SQL> BEGIN
      sys.DBMS_AUDIT_MGMT.set_last_archive_timestamp(
      audit_trail_type  => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
      last_archive_time => SYSTIMESTAMP-7 /* Day */);
END;
/  2    3    4    5    6  
BEGIN
*
ERROR at line 1:
ORA-46258: Cleanup not initialized for the audit trail
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 181
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 996
ORA-06512: at line 2

SQL> EXEC DBMS_AUDIT_MGMT.INIT_CLEANUP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 12)   

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUE);

PL/SQL procedure successfully completed.

SQL>  SELECT COUNT(*) FROM AUD$;

  COUNT(*)
----------
     0

SQL> EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, FALSE)

PL/SQL procedure successfully completed.
其实审计都没有开启，看来原因不在这个上面。

那就查看这个表空间到底都被哪些表占用了。

查看大于100M的数据表
SQL>SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, BYTES / 1024 / 1024  FROM DBA_SEGMENTS  WHERE TABLESPACE_NAME = 'SYSAUX' and  BYTES / 1024 / 1024>100;
发现 wri$_adv_objects 这个表占用了20G
SQL> select count(*) from wri$_adv_objects where task_id=(select distinct id from wri$_adv_tasks where name='AUTO_STATS_ADVISOR_TASK');

  COUNT(*)
----------
 138554669

看来这个表里面记录真不少，这个是因为优化器统计顾问的执行。
找到一个处理方法：

SQL> create table wri$_adv_objects_new as select * from wri$_adv_objects where task_id !=(select distinct id from wri$_adv_tasks where name='AUTO_STATS_ADVISOR_TASK');

Table created.

SQL> select count(*) from wri$_adv_objects_new;

  COUNT(*)
----------
     99504

SQL>  truncate table wri$_adv_objects;

Table truncated.

SQL> insert /*+ APPEND */ into wri$_adv_objects select * from wri$_adv_objects_new;

99504 rows created.

SQL> commit;

Commit complete.

SQL> drop table wri$_adv_objects_new;

Table dropped.

SQL> alter index wri$_adv_objects_idx_01 rebuild;

Index altered.

SQL> alter index wri$_adv_objects_pk rebuild;

Index altered.

SQL> quit

Alert.log里面中终于不报错误了。