Enable and Disable Automatic Snapshots

exec dbms_scheduler.enable(’GATHER_STATS_JOB’) – (Default)

exec dbms_scheduler.disable(’GATHER_STATS_JOB’)

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

we can also display list of stats as follows :

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
11957 1 28/07/09 06:00:31
11958 1 28/07/09 07:00:32
11959 1 28/07/09 08:00:33
11960 1 28/07/09 09:00:34
11961 1 28/07/09 10:00:35
11962 1 28/07/09 11:00:36
11963 1 28/07/09 12:00:37
11964 1 28/07/09 13:00:38
11965 1 28/07/09 14:00:39
11966 1 28/07/09 15:00:40
11967 1 28/07/09 16:00:41
11968 1 28/07/09 17:00:43
11969 1 28/07/09 18:00:43
11970 1 28/07/09 19:00:45
11971 1 28/07/09 20:00:46
11972 1 28/07/09 21:00:46
11973 1 28/07/09 22:00:47
11974 1 28/07/09 23:00:49
11975 1 29/07/09 00:00:50

If you want to change the retention police for the snapshot to 20 days:

exec dbms_workload_repository.modify_snapshot_settings (retention => 20*24*60);