Tag: RETENTION = 8 days ( 11520 Mins ) ( Default )

Check current Retention and Interval

RETENTION = 8 days ( 11520 Mins ) ( Default )
INTERVAL = 1 hour ( 60 Mins ) ( Default )

SQL> desc dba_hist_wr_control
 Name          Null?     Type
 ------------- --------- --------------------------
 DBID          NOT NULL NUMBER
 SNAP_INTERVAL NOT NULL INTERVAL DAY(5) TO SECOND(1)
 RETENTION NOT NULL     INTERVAL DAY(5) TO SECOND(1)
 TOPNSQL                VARCHAR2(10)

SQL> select dbid from v$database;

DBID
----------
2920212463

SQL> col SNAP_INTERVAL format a30
SQL> col RETENTION format a30
SQL> select dbid,SNAP_INTERVAL,RETENTION 
       from dba_hist_wr_control;

 DBID      SNAP_INTERVAL       RETENTION
---------- ------------------- ------------------------------
2920212463 +00000 01:00:00.0   +00008 00:00:00.0

SQL> col SNAP_INTERVAL_MINS format a40
SQL> col RETENTION_MINS format a40
SQL> select dbid,SNAP_INTERVAL*24*60 SNAP_INTERVAL_MINS,
            RETENTION*24*60 RETENTION_MINS 
       from dba_hist_wr_control;

 DBID      SNAP_INTERVAL_MINS            RETENTION_MINS
---------- ----------------------------  -----------------------------
2920212463 +000000060 00:00:00.000000000 +000011520 00:00:00.000000000

Change Retention and Interval

Retention from 8 days (  11520 Mins )  —-> 31 days ( 44640 Mins )
Interval from 1 hour ( 60 Mins ) —-> 0.5 hour ( 30 Mins )

SQL>exec dbms_workload_repository.modify_snapshot_settings
                                 (retention=>44640,
                                  interval=>30,
                                      dbid=>2920212463);

PL/SQL procedure successfully completed.

Check New Retention and Interval

SQL> select dbid,SNAP_INTERVAL,RETENTION 
       from dba_hist_wr_control;

DBID S     NAP_INTERVAL      RETENTION
---------- ----------------- ---------------------
2920212463 +00000 00:30:00.0 +00031 00:00:00.0

SQL> select dbid,SNAP_INTERVAL*24*60 SNAP_INTERVAL_MINS,
                     RETENTION*24*60 RETENTION_MINS 
       from dba_hist_wr_control;

 DBID SNAP_INTERVAL_MINS                   RETENTION_MINS
---------- ------------------------------- ----------------------------
2920212463 +000000030 00:00:00.000000000   +000044640 00:00:00.000000000

Check SYSAUX Tablespace Usage

SQL> SELECT occupant_name, space_usage_kbytes/1024 MBS 
       FROM V$SYSAUX_OCCUPANTS 
   order by 2;

OCCUPANT_NAME             MBS
------------------------- ----------
TSM                         0
EXPRESSION_FILTER           0
ULTRASEARCH_DEMO_USER       0
...
..
.
SM/ADVISOR                 69.3125
AUDIT_TABLES              136.1875
SM/OPTSTAT                406.4375
SM/AWR                   1633.3125

31 rows selected.