2 Replies Latest reply: Jul 15, 2009 11:49 AM by 251126 RSS

    SYSAUX TABLESPACE reached 100%.

    Gowin_dba
      Hi,

      SYSAUX TABLESPACE reached 100%.

      From the below 2 Queries, Which one will make more sense to get free space in SYSAUX TABLESPACE.

      exec dbms_workload_repository.DROP_SNAPSHOT_RANGE(snap_id_1,snap_id_2);

      exec DBMS_STATS.PURGE_STATS(SYSDATE-7);

      or else, Can you please suggest any wise idea.

      Thanks in advance!!!!
        • 1. Re: SYSAUX TABLESPACE reached 100%.
          251126
          Unless you have the need to keep AWR snapshot data more than a few days, I would run schedule the PURGE_STATS procedure to run daily. The biggest problem with not doing it, as you have seen, is a growing for full SYSAUX tablespace that cannot be shrunk.

          Cheers!

          Jay Caviness

          http://www.grumpy-dba.com
          • 2. Re: SYSAUX TABLESPACE reached 100%.
            Gowin_dba
            Jay,

            Thank you..

            I have modified the retention of old stats to 7 days and purged the stats older than 7 days.I think it will reduce the growth by 50%.

            exec DBMS_STATS.PURGE_STATS(SYSDATE-7);
            exec dbms_workload_repository.modify_snapshot_settings (retention =>10080);