High Table space usage in SYSAUX

In general, DBA should focus on the SYSTEM, SYSAUX, UNDO, TEMP, and SYSTEM tablespaces in addition to the tablespace where business data is stored, the size of the UNDO and TEMP tablespaces is determined by the business conditions of the database. If you do not configure the SYSAUX tablespaces by default, they will expand over time! The SYSAUX tablespace is an auxiliary tablespace of the system tablespace. It mainly stores EM-related content and table statistics, AWR snapshots, and audit information. In my opinion, if your SYSAUX tablespace is larger than 2 GB, so let him lose weight!

I. Use the following statements to query table space usage

 
 

  
  
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','UNDOTBS1','TEMP'); 
 
 

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/1R5593234-0.jpg "border =" 0 "alt =" "/> 2: query the proportion of each category project in the SYSAUX tablespace to the bucket, obviously, AWR snapshots occupy about 2 GB of space and the statistical information is about mb. At the same time, the database disables audit audit_trail, so the audit table aud $ does not occupy space.

 
 

  
  
SELECT occupant_name "Item", 
  
  
       space_usage_kbytes / 1048576 "Space Used (GB)", 
  
  
       schema_name "Schema", 
  
  
       move_procedure "Move Procedure" 
  
  
  FROM v$sysaux_occupants 
  
  
ORDER BY 1 
 
 

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/1R5593L9-1.jpg "border =" 0 "alt =" "/>

3. Modify the retention time of statistical information. The default value is 31 days. Here, the value is changed to 7 days. Expired statistical information is automatically deleted.

 
 

  
  
SQL> select dbms_stats.get_stats_history_retention from dual; 
  
  
 
  
  
GET_STATS_HISTORY_RETENTION 
  
  
--------------------------- 
  
  
                         31 
  
  
 
  
  
SQL> exec dbms_stats.alter_stats_history_retention(7);       
  
  
PL/SQL procedure successfully completed. 
  
  
 
  
  
SQL> select dbms_stats.get_stats_history_retention from dual; 
  
  
 
  
  
GET_STATS_HISTORY_RETENTION 
  
  
--------------------------- 
  
  
                          7 
 
 

4. Change the AWR snapshot retention period to 7 days (7*24*60). The snapshot is collected every hour. You can also view and modify the snapshot on the EM page.

 
 

  
  
SQL> begin 
  
  
         dbms_workload_repository.modify_snapshot_settings ( 
  
  
            interval => 60, 
  
  
            retention => 10080, 
  
  
            topnsql => 100 
  
  
          ); 
  
  
end; 
 
 

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/1R5592138-2.jpg "border =" 0 "alt =" "/>

5. Delete the AWR snapshot and view the SYSAUX tablespace usage again. The tablespace usage is reduced to 38.42%.

 
 

  
  
Select min (snap_id), max (snap_id) from dba_hist_snapshot; // query the minimum and maximum snapshot IDS
  
  
 
  
  
Begin
  
  
Dbms_workload_repository.drop_snapshot_range (
  
  
Low_snap_id => 10758,
  
  
High_snap_id => 10900,
  
  
Dbid => 387090299 );
  
  
End;
 
 

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/1R55a919-3.jpg "border =" 0 "alt =" "/>

Thank you for sharing your reference! Http://jhdba.wordpress.com/2009/05/19/purging-statistics-from-the-sysaux-tablespace/

This article is from the "Bo Yue" blog and will not be reproduced!