If you have to size a new Oracle 10g database, or you just want to increase the AWR snapshot interval, you may want to know how much database space is being used by the current AWR setup.
This is where V$SYSAUX_OCCUPANTS comes in.
SQL> set linesize 300
SQL> col OCCUPANT_NAME format A7
SQL> SELECT OCCUPANT_NAME,
OCCUPANT_DESC,
SPACE_USAGE_KBYTES
FROM v$sysaux_occupants where OCCUPANT_NAME ='SM/AWR';
OCCUPAN OCCUPANT_DESC SPACE_USAGE_KBYTES
------- ----------------------------------------------------- ------------------
SM/AWR Server Manageability - Automatic Workload Repository 107008
The above was taken from a system with a default AWR setup (retention of 7 days, snapping every hour).
This is approximately 14MB per day.
If you were thinking you were going to keep 60 days, you would expect about 900MB of data in SYSAUX.
