If tablespace sysaux is rapidly growing
Sometimes, expecially for new databases, DBAs can face with a rapidly growing SYSAUX tablespace.
In most of the cases this is due to AWR snapshot information, so you should adjust SYSAUX space in order to keep it. Typically a size between 500MB and 1GB should be adequate, because AWR snapshot space is reused based on the AWR retention time (default: 7 days), and AWR creates a snapshot with a specified frequency (default: every hour):
SQL> SELECT RETENTION FROM DBA_HIST_WR_CONTROL;
RETENTION
—————————————————————————
+00007 00:00:00.0
SQL> SELECT SNAP_INTERVAL FROM DBA_HIST_WR_CONTROL;
SNAP_INTERVAL
—————————————————————————
+00000 01:00:00.0
You can modify these parameters using the DBMS_WORKLOAD_REPOSITORY, for example:
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(RETENTION=>7200) — in minutes (5 days)
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>30) — in minutes (half an hour)
You could also verify the value of the STATISTICS_LEVEL parameter, because the default value is TYPICAL and enough for AWR to work, but if you set it to ALL much more information is kept in the snapshots:
SQL> show parameter STATISTICS_LEVEL
NAME TYPE VALUE
———————————— ———– ——————————
statistics_level string TYPICAL
But if you are not sure if AWR is the issue, you can try to execute this useful query in order to see what actually is in the SYSAUX tablespace:
SET PAGESIZE 1000
SET LINESIZE 200
COL SPACE_MB 999G999G999D99
COL SCHEMA FORMAT A20
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ‘,.’
/
SELECT INST_ID, OCCUPANT_DESC, ROUND(SPACE_USAGE_KBYTES/1024,2) SPACE_MB,
SUBSTR(SCHEMA_NAME,1,30) SCHEMA
FROM GV$SYSAUX_OCCUPANTS
ORDER BY 3 DESC
/
A sample output is following:
INST_ID OCCUPANT_DESC SPACE_MB SCHEMA
———- —————————————————————- ———- ——————–
1 Server Manageability – Automatic Workload Repository 57,31 SYS
1 Enterprise Manager Repository 48,69 SYSMAN
1 XDB 48,25 XDB
1 Oracle Spatial 41 MDSYS
1 Analytical Workspace Object Table 26,06 SYS
1 OLAP API History Tables 26,06 SYS
1 OLAP Catalog 15,56 OLAPSYS
1 Server Manageability – Optimizer Statistics History 12,75 SYS
1 Server Manageability – Advisor Framework 7,88 SYS
1 Workspace Manager 6,38 WMSYS
1 LogMiner 6 SYSTEM
1 Server Manageability – Other Components 4,81 SYS
1 Oracle Text 4,63 CTXSYS
1 Expression Filter System 3,63 EXFSYS
1 Enterprise Manager Monitoring User 1,75 DBSNMP
1 Logical Standby ,88 SYSTEM
1 Oracle Streams ,5 SYS
1 Oracle interMedia ORDSYS Components ,5 ORDSYS
1 Unified Job Scheduler ,5 SYS
1 Oracle Data Mining ,25 DMSYS
1 Oracle Transparent Session Migration User ,25 TSMSYS
1 Oracle Ultra Search Demo User 0 WK_TEST
1 Oracle interMedia ORDPLUGINS Components 0 ORDPLUGINS
1 Statspack Repository 0 PERFSTAT
1 Oracle Ultra Search 0 WKSYS
1 Oracle interMedia SI_INFORMTN_SCHEMA Components 0 SI_INFORMTN_SCHEMA
