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
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
11976          1 29/07/09 01:00:52
11977          1 29/07/09 02:00:52
11978          1 29/07/09 03:00:53
11979          1 29/07/09 04:00:54
11980          1 29/07/09 05:00:55
11981          1 29/07/09 06:00:56
11982          1 29/07/09 07:00:57
11983          1 29/07/09 08:00:58
11984          1 29/07/09 09:00:59
11985          1 29/07/09 10:00:01
11986          1 29/07/09 11:00:02
11987          1 29/07/09 12:00:02
11988          1 29/07/09 13:00:03
11989          1 29/07/09 14:00:04
11990          1 29/07/09 15:00:05
11991          1 29/07/09 16:00:07
11992          1 29/07/09 17:00:08
11993          1 29/07/09 18:00:09
11994          1 29/07/09 19:00:10
11995          1 29/07/09 20:00:11
11996          1 29/07/09 21:00:12
11997          1 29/07/09 22:00:12
11998          1 29/07/09 23:00:14
11999          1 30/07/09 00:00:15
12000          1 30/07/09 01:00:16
12001          1 30/07/09 02:00:17
12002          1 30/07/09 03:00:18
12003          1 30/07/09 04:00:19
12004          1 30/07/09 05:00:20
12005          1 30/07/09 06:00:21
12006          1 30/07/09 07:00:22
12007          1 30/07/09 08:00:23
12008          1 30/07/09 09:00:24
12009          1 30/07/09 10:00:25
12010          1 30/07/09 11:00:26
12011          1 30/07/09 12:00:27
12012          1 30/07/09 13:00:28
12013          1 30/07/09 14:00:29
12014          1 30/07/09 15:00:30
12015          1 30/07/09 16:00:31
12016          1 30/07/09 17:00:32
12017          1 30/07/09 18:00:33
12018          1 30/07/09 19:00:34
12019          1 30/07/09 20:00:35
12020          1 30/07/09 21:00:36
12021          1 30/07/09 22:00:38
12022          1 30/07/09 23:00:39
12023          1 31/07/09 00:00:40
12024          1 31/07/09 01:00:42
12025          1 31/07/09 02:00:43
12026          1 31/07/09 03:00:44
12027          1 31/07/09 04:00:45
12028          1 31/07/09 05:00:46
12029          1 31/07/09 06:00:47
12030          1 31/07/09 07:00:47
12031          1 31/07/09 08:00:48
12032          1 31/07/09 09:00:49
12033          1 31/07/09 10:00:51
12034          1 31/07/09 11:00:52
12035          1 31/07/09 12:00:53
12036          1 31/07/09 13:00:54
12037          1 31/07/09 14:00:55
12038          1 31/07/09 15:00:56
12039          1 31/07/09 16:00:57
12040          1 31/07/09 17:00:58
12041          1 31/07/09 18:00:59
12042          1 31/07/09 19:00:00
12043          1 31/07/09 20:00:01
12044          1 31/07/09 21:00:02
12045          1 31/07/09 22:00:03
12046          1 31/07/09 23:00:04
12047          1 01/08/09 00:00:06
12048          1 01/08/09 01:00:07
12049          1 01/08/09 02:00:08
12050          1 01/08/09 03:00:09
12051          1 01/08/09 04:00:10
12052          1 01/08/09 05:00:10
12053          1 01/08/09 06:00:11
12054          1 01/08/09 07:00:13
12055          1 01/08/09 08:00:14
12056          1 01/08/09 09:00:15
12057          1 01/08/09 10:00:16
12058          1 01/08/09 11:00:17
12059          1 01/08/09 12:00:18
12060          1 01/08/09 13:00:19
12061          1 01/08/09 14:00:20
12062          1 01/08/09 15:00:21
12063          1 01/08/09 16:00:22
12064          1 01/08/09 17:00:23
12065          1 01/08/09 18:00:24
12066          1 01/08/09 19:00:25
12067          1 01/08/09 20:00:26
12068          1 01/08/09 21:00:27
12069          1 01/08/09 22:00:28
12070          1 01/08/09 23:00:29
12071          1 02/08/09 00:00:30
12072          1 02/08/09 01:00:32
12073          1 02/08/09 02:00:33
12074          1 02/08/09 03:00:34
12075          1 02/08/09 04:00:35
12076          1 02/08/09 05:00:36
12077          1 02/08/09 06:00:37
12078          1 02/08/09 07:00:38
12079          1 02/08/09 08:00:39
12080          1 02/08/09 09:00:40
12081          1 02/08/09 10:00:41
12082          1 02/08/09 11:00:42
12083          1 02/08/09 12:00:43
12084          1 02/08/09 13:00:44
12085          1 02/08/09 14:00:45
12086          1 02/08/09 15:00:46
12087          1 02/08/09 16:00:47
12088          1 02/08/09 17:00:47
12089          1 02/08/09 18:00:48
12090          1 02/08/09 19:00:49
12091          1 02/08/09 20:00:50
12092          1 02/08/09 21:00:51
12093          1 02/08/09 22:00:52
12094          1 02/08/09 23:00:53
12095          1 03/08/09 00:00:54
12096          1 03/08/09 01:00:56
12097          1 03/08/09 02:00:57
12098          1 03/08/09 03:00:58
12099          1 03/08/09 04:00:59
12100          1 03/08/09 05:00:00
12101          1 03/08/09 06:01:01
12102          1 03/08/09 07:00:02
12103          1 03/08/09 08:00:03
12104          1 03/08/09 09:00:04
12105          1 03/08/09 10:00:05
12106          1 03/08/09 11:00:06
12107          1 03/08/09 12:00:07
12108          1 03/08/09 13:00:08
12109          1 03/08/09 14:00:09
12110          1 03/08/09 15:00:10
12111          1 03/08/09 16:00:11
12112          1 03/08/09 17:00:12
12113          1 03/08/09 18:00:13
12114          1 03/08/09 19:00:14
12115          1 03/08/09 20:00:15
12116          1 03/08/09 21:00:16
12117          1 03/08/09 22:00:17
12118          1 03/08/09 23:00:18
12119          1 04/08/09 00:00:19
12120          1 04/08/09 01:00:20
12121          1 04/08/09 02:00:22
12122          1 04/08/09 03:00:22
12123          1 04/08/09 04:00:23
12124          1 04/08/09 05:00:24
12125          1 04/08/09 06:00:25
12126          1 04/08/09 07:00:26
12127          1 04/08/09 08:00:28
12128          1 04/08/09 09:00:29
12129          1 04/08/09 10:00:30
12130          1 04/08/09 11:00:32
12131          1 04/08/09 12:00:33
12132          1 04/08/09 13:00:33
12133          1 04/08/09 14:00:34
12134          1 04/08/09 15:00:35

185 rows selected.

SQL>

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);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s