1. What is the SYSAUX Tablespace?
The SYSAUX tablespace is an auxiliary tablespace for the Oracle database, primarily used to store data for the following components:
- AWR (Automatic Workload Repository)
- Statspack
- Oracle Text
- Oracle Streams
- SQL Tuning Advisor
- Other database components
When the SYSAUX tablespace grows excessively, it can consume disk space and potentially impact database performance. This article explains how to address this issue.
2. Checking SYSAUX Tablespace Usage
To check the size and usage rate of the SYSAUX tablespace, execute the following SQL:
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb, maxbytes/1024/1024 AS max_size_mb
FROM dba_data_files
WHERE tablespace_name = 'SYSAUX';
Additionally, to check the usage by component within the SYSAUX tablespace, use the following SQL:
SELECT occupant_name, space_usage_kbytes/1024 AS space_usage_mb
FROM v$sysaux_occupants
ORDER BY space_usage_mb DESC;
Based on these results, identify which components are consuming a large amount of the tablespace.
SQL> SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb, maxbytes/1024/1024 AS max_size_mb
2 FROM dba_data_files
3 WHERE tablespace_name = 'SYSAUX';
TABLESPACE_NAME FILE_NAME SIZE_MB MAX_SIZE_MB
-------------------- ------------------------------------------- ---------- -----------
SYSAUX /u01/app/oracle/oradata/V19/sysaux01.dbf 760 32767.9844
SQL> SELECT occupant_name, space_usage_kbytes/1024 AS space_usage_mb
2 FROM v$sysaux_occupants
3 ORDER BY space_usage_mb DESC;
OCCUPANT_NAME SPACE_USAGE_MB
-------------------------------------------------- --------------
SDO 231.8125
XDB 68.375
SM/AWR 60.0625
SM/OTHER 48.875
AO 45.8125
AUDSYS 44.4375
LOGMNR 11
SM/OPTSTAT 9.9375
SM/ADVISOR 9.4375
WM 6.5625
PL/SCOPE 2.8125
SQL_MANAGEMENT_BASE 2.6875
TEXT 2.6875
JOB_SCHEDULER 1.9375
STREAMS 1.6875
LOGSTDBY 1.5625
AUTO_TASK .5625
EM_MONITORING_USER .375
SMON_SCN_TIME .3125
ORDIM/ORDDATA 0
ORDIM/ORDPLUGINS 0
ORDIM/SI_INFORMTN_SCHEMA 0
EM 0
XSAMD 0
ULTRASEARCH 0
ULTRASEARCH_DEMO_USER 0
EXPRESSION_FILTER 0
TSM 0
AUDIT_TABLES 0
STATSPACK 0
XSOQHIST 0
ORDIM 0
32行が選択されました。
2.1 Major Components Visible in v$sysaux_occupants
| Component Name | Description |
| AWR (Automatic Workload Repository) | Snapshot data for the Automatic Workload Repository |
| Statspack | Snapshots for performance monitoring |
| SQL Tuning Advisor | Data for the SQL Tuning Advisor |
| SQL Access Advisor | Data for the SQL Access Advisor |
| Oracle Text | Metadata for text search features |
| Oracle Streams | Metadata for Streams replication |
| XDB | Metadata for XML Database |
| Workspace Manager | Data for versioning and workspace management |
| Enterprise Manager Repository | Data for Oracle Enterprise Manager |
| Unified Auditing | Log data for Unified Auditing |
By referring to this table, you can manage SYSAUX tablespace usage by deleting unnecessary components or changing settings.
3. Checking and Deleting AWR Snapshots
3.1 Confirming Stored AWR Snapshots
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY begin_interval_time DESC;
SQL> SELECT snap_id, begin_interval_time, end_interval_time
2 FROM dba_hist_snapshot
3 ORDER BY begin_interval_time DESC;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ----------------------------------- -----------------------------------
19 25-02-11 18:40:20.104 25-02-11 19:00:24.833
18 25-02-11 18:20:15.495 25-02-11 18:40:20.104
17 25-02-11 18:00:10.560 25-02-11 18:20:15.495
16 25-02-11 17:40:05.824 25-02-11 18:00:10.560
15 25-02-11 17:20:01.157 25-02-11 17:40:05.824
14 25-02-11 17:00:56.584 25-02-11 17:20:01.157
13 25-02-11 16:40:51.904 25-02-11 17:00:56.584
12 25-02-11 16:20:47.237 25-02-11 16:40:51.904
11 25-02-11 16:00:42.240 25-02-11 16:20:47.237
10 25-02-11 15:40:37.504 25-02-11 16:00:42.240
9 25-02-11 15:20:32.775 25-02-11 15:40:37.504
8 25-02-11 15:00:27.847 25-02-11 15:20:32.775
7 25-02-11 14:40:23.177 25-02-11 15:00:27.847
6 25-02-11 14:20:18.185 25-02-11 14:40:23.177
5 25-02-11 14:00:13.190 25-02-11 14:20:18.185
4 25-02-11 13:40:08.265 25-02-11 14:00:13.190
3 25-02-11 13:21:04.308 25-02-11 13:40:08.265
2 25-02-11 13:10:03.000 25-02-11 13:21:04.308
1 25-02-11 01:29:46.000 25-02-11 01:40:49.795
19行が選択されました。
3.2 Checking the AWR Snapshot Retention Period
SELECT retention FROM dba_hist_wr_control;
SQL> SELECT retention FROM dba_hist_wr_control;
RETENTION
----------------------------------------
+00010 00:00:00.0
3.3 Shortening the Retention Period (Example: Changing to 8 days)
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
retention => 11520, -- 8 days (in minutes)
interval => 60 -- Take a snapshot every 1 hour (60 minutes)
);
END;
/
SQL> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
3 retention => 11520, -- 8日（分単位）
4 interval => 60 -- 1時間ごとにスナップショットを取得
5 );
6 END;
7 /
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT retention FROM dba_hist_wr_control;
RETENTION
----------------------------------------
+00008 00:00:00.0
3.4 Deleting Old Snapshots
exec dbms_workload_repository.drop_snapshot_range(1, 5);
Example: Deleting SNAP_IDs from 1 to 5.
Performing this process regularly can prevent the SYSAUX tablespace from growing excessively.
SQL> SELECT snap_id, begin_interval_time, end_interval_time
2 FROM dba_hist_snapshot
3 ORDER BY begin_interval_time DESC;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ----------------------------------- -----------------------------------
19 25-02-11 18:40:20.104 25-02-11 19:00:24.833
18 25-02-11 18:20:15.495 25-02-11 18:40:20.104
17 25-02-11 18:00:10.560 25-02-11 18:20:15.495
16 25-02-11 17:40:05.824 25-02-11 18:00:10.560
15 25-02-11 17:20:01.157 25-02-11 17:40:05.824
14 25-02-11 17:00:56.584 25-02-11 17:20:01.157
13 25-02-11 16:40:51.904 25-02-11 17:00:56.584
12 25-02-11 16:20:47.237 25-02-11 16:40:51.904
11 25-02-11 16:00:42.240 25-02-11 16:20:47.237
10 25-02-11 15:40:37.504 25-02-11 16:00:42.240
9 25-02-11 15:20:32.775 25-02-11 15:40:37.504
8 25-02-11 15:00:27.847 25-02-11 15:20:32.775
7 25-02-11 14:40:23.177 25-02-11 15:00:27.847
6 25-02-11 14:20:18.185 25-02-11 14:40:23.177
5 25-02-11 14:00:13.190 25-02-11 14:20:18.185
4 25-02-11 13:40:08.265 25-02-11 14:00:13.190
3 25-02-11 13:21:04.308 25-02-11 13:40:08.265
2 25-02-11 13:10:03.000 25-02-11 13:21:04.308
1 25-02-11 01:29:46.000 25-02-11 01:40:49.795
19行が選択されました。
SQL> exec dbms_workload_repository.drop_snapshot_range(1, 5);
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT snap_id, begin_interval_time, end_interval_time
2 FROM dba_hist_snapshot
3 ORDER BY begin_interval_time DESC;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ----------------------------------- -----------------------------------
19 25-02-11 18:40:20.104 25-02-11 19:00:24.833
18 25-02-11 18:20:15.495 25-02-11 18:40:20.104
17 25-02-11 18:00:10.560 25-02-11 18:20:15.495
16 25-02-11 17:40:05.824 25-02-11 18:00:10.560
15 25-02-11 17:20:01.157 25-02-11 17:40:05.824
14 25-02-11 17:00:56.584 25-02-11 17:20:01.157
13 25-02-11 16:40:51.904 25-02-11 17:00:56.584
12 25-02-11 16:20:47.237 25-02-11 16:40:51.904
11 25-02-11 16:00:42.240 25-02-11 16:20:47.237
10 25-02-11 15:40:37.504 25-02-11 16:00:42.240
9 25-02-11 15:20:32.775 25-02-11 15:40:37.504
8 25-02-11 15:00:27.847 25-02-11 15:20:32.775
7 25-02-11 14:40:23.177 25-02-11 15:00:27.847
6 25-02-11 14:20:18.185 25-02-11 14:40:23.177
14行が選択されました。
4. Checking and Deleting Statspack Data
4.1 Confirming Stored Statspack Snapshots
SELECT snap_id, snap_time
FROM stats$snapshot
ORDER BY snap_time DESC;
SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
セッションが変更されました。
SQL> SELECT snap_id, snap_time
2 FROM stats$snapshot
3 ORDER BY snap_time DESC;
SNAP_ID SNAP_TIME
---------- -------------------
31 2025/02/11 19:28:08
26 2025/02/11 18:58:08
25 2025/02/11 18:28:08
24 2025/02/11 17:58:08
23 2025/02/11 17:28:08
22 2025/02/11 16:58:08
21 2025/02/11 16:28:08
14 2025/02/11 15:58:08
13 2025/02/11 15:28:08
12 2025/02/11 14:58:08
11 2025/02/11 14:28:09
1 2025/02/11 13:58:09
12行が選択されました。
4.2 Checking the Number of Statspack Snapshots
SELECT COUNT(*) FROM stats$snapshot;
4.3 Deleting Old Snapshots
EXEC statspack.purge(i_num_days => 7);
Deleting Statspack snapshots can reduce the usage of the SYSAUX tablespace.
5. Deleting Unused Components
You can secure free space by deleting unnecessary components stored in the SYSAUX tablespace.
5.1 Checking Unused Components
SELECT occupant_name, space_usage_kbytes/1024 AS space_usage_mb
FROM v$sysaux_occupants
ORDER BY space_usage_mb DESC;
5.2 Deleting Other Components
If there are features you are not using, disable or delete the components as appropriate.
6. Summary
If the SYSAUX tablespace becomes bloated, you can reduce usage by implementing the following measures:
- Check SYSAUX tablespace usage.
- Check and delete AWR snapshots.
- Delete old Statspack data.
- Delete unnecessary components.
- Shrink the SYSAUX tablespace.
By performing these actions regularly, you can maintain database performance and properly manage disk space. It is important to adjust settings according to your database operation policy to reduce wasted space.
This article is based on Oracle Database 19c (screens and default values may differ in other versions).
[reference]
Oracle Database Database Administrator’s Guide, 19c
コメント