See the oldest statistics history available SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL; GET_STATS_HISTORY_AVAILABILITY --------------------------------------------------------------------------- 25-OCT-11 10.00.55.093000000 PM -07:00 See the stats retention SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION --------------------------- 31 Modify retention SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60); Space currently used to store statistics in SYSAUX in KBytes, so increasing the retention would affect it's size SQL> select occupant_desc, … Continue reading How to view/change statistics retention and space used to store stats history?
Tag: retention
How to view and change STATS retention?
Way of changing retention SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION --------------------------- 31 -- value passed is in days -- If 0 is passed - old statistics are never saved. The automatic purge will delete all statistics history -- If 1 is passed - statistics history is never purged by automatic purge. SQL> exec dbms_stats.alter_stats_history_retention(30); PL/SQL … Continue reading How to view and change STATS retention?
How to change/clear retention policy for RMAN?
Using CLEAR one can set the retention policy to default and using NONE have no retention policy. The example below shows the difference between the two. --------- Display retention policy and shows using the option CLEAR ---------- RMAN> show retention policy; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default RMAN> configure … Continue reading How to change/clear retention policy for RMAN?
How to change AWR retention, interval, topnsql?
Using dbms_workload_repository.modify_snapshot_settings, one can modify retention, interval, and topnsql. 1- Get the dbid which is needed to passs to dbms_workload_repository.modify_snapshot_settings SQL> select dbid from v$database; DBID ---------- 1992878807 2- Set retention, interval and top sql: retention=>value in minutes so (45 days * 24 (hours per day) * 60 minutes per hour = 64800), max value … Continue reading How to change AWR retention, interval, topnsql?
How to find AWR snapshot interval and retention settings?
Using this SQL one can find the snapshot interval and snapshot retention. SQL> SELECT extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_Interval, extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_Interval FROM dba_hist_wr_control; Snapshot_Interval Retention_Interval ----------------- ------------------ 60 10080 How to change AWR snapshot interval and/or retention
How to Display a List of All Snapshots from OMS
SQL> set lines 100 pages 999 SQL> select snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') starting 2 from dba_hist_snapshot 3 order by 1; SNAP_ID SNAP_LEVEL STARTING ---------- ---------- ----------------- 11950 1 27/07/09 23:00:24 11951 1 28/07/09 00:00:25 11952 1 28/07/09 01:00:26 11953 1 28/07/09 02:00:27 11954 1 28/07/09 03:00:28 11955 1 28/07/09 04:00:30 11956 1 28/07/09 05:00:31 … Continue reading How to Display a List of All Snapshots from OMS
alexlima.com 