SYSAUX tablespace is growing so much. how can i protect it ?

SYSAUX tablespace is growing so much. how can i protect it ?
=====================================================

AWR is identified to be consuming more space.

SQL> select * from v$sysaux_occupants ;

SQL> SELECT occupant_name, occupant_desc, space_usage_kbytes

FROM v$sysaux_occupants

ORDER BY space_usage_kbytes DESC


OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES

SM/AWR Server Manageability - Automatic Workload Repository 19906624
SM/OPTSTAT Server Manageability - Optimizer Statistics History 191808
EM Enterprise Manager Repository 59648


Solution

1.)
----------------see the snapshots interval and retention period time

SQL> select * from DBA_HIST_WR_CONTROL;

Check whether the retention period is set too high. In this case, it is set to 4 and so no need to modify the retention.

If it is set to high value, then consider reducing the retention period to avoid growing of space.

By default, it is set to 7 days.

The new retention time is specified in minutes.

SQL> execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 4320);

Here 4320 is 3*24*60 ( Convert the days to minutes). For 3 days, it 4320 minutes.



2). Next thing is to check the amount of snapshots have been generated and available.

If this is too high, then try to drop the unwanted snapshots to reclaim the space.


SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id,high_snap_id);

low_snap_id : The low snapshot id of snapshots to drop.

high_snap_id : The high snapshot id of snapshots to drop.



3) Then check whether the statistics_level parameter is set to typical.


SQL> show parameter statistics


NAME TYPE VALUE

------------------------------------ ----------- -----------------------------

statistics_level string ALL <= Should be set TYPICAL timed_os_statistics integer 5 timed_statistics boolean TRUE If it is set to ALL, please change it to TYPICAL. Because statistics_level=ALL will gather lot of additional information in AWR repository which would consume more space. Most of the cases, if the statistics_level is set to TYPICAL then the growth would be stopped. SQL> alter system set statistics_level=typical; ---dynamic parameter