SYSTEM tablespace AUD $ solution to excessive space usage
Problem: The SYSTEM tablespace usage exceeds 99%. During the preliminary design, the SYSTEM tablespace is fixed to 10 GB and cannot be automatically expanded.
1. query the usage of the SYSTEM tablespace.
set linesize 200col TABLESPACE_NAME for a25col SUM_SPACE(M) for a20col USED_SPACE(M) for a20col USED_RATE(%) for a20col FREE_SPACE(M) for a20SELECT 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(+) AND D.TABLESPACE_NAME = 'SYSTEM';
2. query the space occupied by each segment in the SYSTEM tablespace.
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, SUM(BYTES)/1024/1024 FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'SYSTEM' GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE ORDER BY 4;
3. truncate the AUD $ table with the largest occupied space (the operation must be confirmed with the DBA)
truncate table AUD$;
4. query the tablespace of the AUD $ table
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME = 'AUD$' AND OWNER = 'SYS';
5. Change the tablespace of the AUD $ table to another tablespace (selected)
BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_LOCATION_VALUE => '&AUD_TBS_NAME'); END; /