High Table space usage in SYSAUX
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, if SYSAUX tablespace is not configured by default, it will expand over time.
After several times of continuous expansion, the table space of SYSAUX has been increased. Currently, 20 Gbit/s, so it is time to consider losing weight.
1. View table space usage
SQL>
Select Tablespace_Name,
Sum_m,
Max_m,
Count_Blocks Free_Blk_Cnt,
Sum_Free_m,
To_Char (100 * Sum_Free_m/Sum_m, '99. 9999 ') |' % 'As Pct_Free,
100-To_Char (100 * Sum_Free_m/Sum_m, '99. 9999 ') |' % 'As Pct_used
From (Select Tablespace_Name, Sum (Bytes)/1024/1024 AsSum_m
From Dba_Data_Files
Group By Tablespace_Name)
Left Join
(Select Tablespace_Name As Fs_Ts_Name,
Max (Bytes)/1024/1024 As Max_m,
Count (Blocks) As Count_Blocks,
Sum (Bytes/1024/1024) As Sum_Free_m
From Dba_Free_Space
Group ByTablespace_Name)
On Tablespace_Name = Fs_Ts_Name
Order by Sum_Free_m/Sum_m;
TABLESPACE_NAME SUM_M MAX_M FREE_BLK_CNT SUM_FREE_MPCT_FREE PCT_USED
---------------------------------------------------------------------------------------------
SYSAUX 21652 1031 13 1032.5 4.7686% 95.2314%
NTICKET_DB 235520 7798 4922 46894 19.9108%
NTICKET_INDEX 112640 2418 764 25382 22.5337%
SYSTEM 2798 1884 7 1916.125 68.4820% 31.518%
USERS 5 3.6875 1 3.6875 73.7500% 26.25%
PORTALMAIL 200 168.125 2 170 85.0000% 15%
UNDOTBS2 10265 3596 200 9936.8125 96.8028%
UNDOTBS3 10265 3593 248 9983 97.2528%
UNDOTBS1 24455 3968 184 24280.625. 99.2870%
STRATEGY 20480 3968 6 20478 99.9902%. 0098%
2. view the proportion of each category project in the SYSAUX tablespace to the storage space. We can see that AWR snapshots occupy about 20 GB of space and the statistical information is about mb. Meanwhile, the database disables audit audit_trail, therefore, the audit table aud $ does not occupy space.
SQL> col Item For a30
SQL> col "Space Used (GB)" For a10
SQL> col Schema For a20
SQL> col "MoveProcedure" For a200
SQL>
SQL> SELECT occupant_name "Item ",
Round (space_usage_kbytes/1024/1024, 3) "Space Used (GB )",
Schema_name "Schema ",
Move_procedure "MoveProcedure"
FROM v $ sysaux_occupants
Order by 2 Desc;
Item Space UsedSchema Move Procedure
---------------------------------------------------------------------------------------------------
SM/AWR 19.103 SYS
SM/OPTSTAT 0.311 SYS
SM/ADVISOR 0.248 SYS
XDB 0.124 XDB. DBMS_XDB.MOVEXDB_TABLESPACE
SDO 0.073 MDSYS. MOVE_SDO
EM 0.045 SYSMAN emd_maintenance.move_em_tblspc
XSOQHIST 0.037 SYS DBMS_XSOQ.OlapiMoveProc
AO 0.037SYS DBMS_AW.MOVE_AWMETA
ORDIM/ORDDATA 0.013 ORDDATA ordsys. ord_admin.move_ordim_tblspc
LOGMNR 0.013 system sys. DBMS_LOGMNR_D.SET_TABLESPACE
AUDIT_TABLES 0 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables
3. Modify the retention time of statistical information. The default value is 31 days. Here, the value is changed to 7 days. Expired statistics are automatically deleted.
SQL> selectdbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
SQL> exec dbms_stats.alter_stats_history_retention (15 );
PL/SQL procedure successfullycompleted
SQL> selectdbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
15
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.
-- Check that the current system is retained for 8 days, sampling once an hour
SQL> select * fromdba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
--------------------------------------------------------------------------------------------------
1494575446 + 0000001: 00: 00.0 + 0000800: 00: 00.0 DEFAULT
--
SQL>
Begin
Dbms_workload_repository.modify_snapshot_settings (
Interval => 60,
Retention => 10080, -- minute
Topnsql = & gt; 100
);
End;
ORA-13541: system move window baseline size (691200) greater than retention time (604800)
ORA-06512: In "SYS. DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: In "SYS. DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: In line 2
Here, 691200 (8*24*60*60) and 604800 (7*24*60*60) are in seconds.
An error is reported because the current system moving window is larger than the current time window.
-- View the current MOVING_WINDOW_SIZE of the system,
SQL> selectdbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;
DBID BASELINE_NAME BASELINE_TYPEMOVING_WINDOW_SIZE
----------------------------------------------------------------------------------
1494575446SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
-- Modify the size to 7, that is, 7 days.
SQL> execdbms_workload_repository.modify_baseline_window_size (7 );
PL/SQL procedure successfullycompleted
-- Modify the retention time of the AWR snapshot again
SQL> execDBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (interval => 60, retention => 7*24*60 );
PL/SQL procedure successfullycompleted
Note: You can also use EM to modify AWR settings.
5. Delete the AWR snapshot and view the SYSAUX tablespace usage again.
-- Query the minimum and maximum snapshot IDS
SQL> selectmin (snap_id), max (snap_id) from dba_hist_snapshot;
MIN (SNAP_ID) MAX (SNAP_ID)
------------------------
8188 8397
-- Delete the oldest 24 AWR snapshots, that is, the oldest 24-hour snapshot.
SQL> execdbms_workload_repository.drop_snapshot_range (low_snap_id => 8188, high_snap_id => 8188 + 24 );
Finally, check the tablespace again and find that the usage has been reduced.
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.