Oracle 12c sysaux 空间清理

1、查询 sysaux使用情况

SELECT *  FROM (SELECT D.TABLESPACE_NAME,  SPACE || 'M' "SUM_SPACE(M)",  BLOCKS "SUM_BLOCKS",  SPACE - NVL(FREE_SPACE, 0) || 'M' "USED_SPACE(M)",  ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",  FREE_SPACE || 'M' "FREE_SPACE(M)"  FROM (SELECT TABLESPACE_NAME,  ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,  SUM(BLOCKS) BLOCKS  FROM DBA_DATA_FILES  GROUP BY TABLESPACE_NAME) D,  (SELECT TABLESPACE_NAME,  ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE  FROM DBA_FREE_SPACE  GROUP BY TABLESPACE_NAME) F  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  UNION ALL  SELECT D.TABLESPACE_NAME,  SPACE || 'M' "SUM_SPACE(M)",  BLOCKS SUM_BLOCKS,  USED_SPACE || 'M' "USED_SPACE(M)",  ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",  NVL(FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"  FROM (SELECT TABLESPACE_NAME,  ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,  SUM(BLOCKS) BLOCKS  FROM DBA_TEMP_FILES  GROUP BY TABLESPACE_NAME) D,  (SELECT TABLESPACE_NAME,  ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,  ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE  FROM V$TEMP_SPACE_HEADER  GROUP BY TABLESPACE_NAME) F  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  ORDER BY 1)  WHERE TABLESPACE_NAME IN ('SYSAUX', 'SYSTEM');

2、查询使用空间比较大的表

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;

3、清理

begin
dbms_audit_mgmt.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp  =>  FALSE);
end;