删除AWR中快照

下面这个脚本是实现在SHELL中查询AWR快照的ID号，并且手动输入快照ID号来删除相应的快照。
脚本的运行环境是：SOLARIS 10U8 64，ORACLE 10.2.0.4

$ cat awrdelete
#!/usr/bin/ksh
echo ""
echo ""
echo ""
$1<<EOF
set lines 175
col instart_startup  for a20
col inst_name  for a10
col  db_name  for a10
col snap_id  for 9999999
col snapdat  for a20

select to_char(s.startup_time,  'yyyy-mm-dd HH24:mi:ss') instart_startup,
             di.instance_name inst_name,
             di. db_name  db_name,
             s.snap_id snap_id,
             to_char(s.end_interval_time,  'yyyy-mm-dd HH24:mi:ss') snapdat,
             s.snap_level lvl
     from dba_hist_snapshot s, dba_hist_database_instance di
where di.dbid = s.dbid
      and di.instance_number = s.instance_number
      and di.startup_time = s.startup_time
order  by  db_name, instance_name, snap_id
/
exit
EOF
echo    "please input  begin snap id:"
read begin_id
echo    "please input  end snap id:"
read end_id
$1<<EOF
set lines 175
col instart_startup  for a20
col inst_name  for a10
col  db_name  for a10
col snap_id  for 9999999
col snapdat  for a20
exec dbms_workload_repository.drop_snapshot_range($begin_id,$end_id)

select to_char(s.startup_time,  'yyyy-mm-dd HH24:mi:ss') instart_startup,
             di.instance_name inst_name,
             di. db_name  db_name,
             s.snap_id snap_id,
             to_char(s.end_interval_time,  'yyyy-mm-dd HH24:mi:ss') snapdat,
             s.snap_level lvl
     from dba_hist_snapshot s, dba_hist_database_instance di
where di.dbid = s.dbid
      and di.instance_number = s.instance_number
      and di.startup_time = s.startup_time
order  by  db_name, instance_name, snap_id
/
exit
EOF

下面是测试效果
$ awrdelete htzraw




INSTART_STARTUP      INST_NAME  DB_NAME     SNAP_ID SNAPDAT                     LVL
-------------------- ---------- ---------- -------- -------------------- ----------
2012-08-13 12:23:26  htzraw     HTZRAW          245 2012-08-14 01:12:09           1
2012-08-15 13:53:55  htzraw     HTZRAW          248 2012-08-15 14:05:05           1
2012-08-15 13:53:55  htzraw     HTZRAW          249 2012-08-15 14:29:31           1
2012-08-15 13:53:55  htzraw     HTZRAW          250 2012-08-15 14:34:14           1

please input begin snap id:
245
please input end snap id:
248

PL/SQL procedure successfully completed.


INSTART_STARTUP      INST_NAME  DB_NAME     SNAP_ID SNAPDAT                     LVL
-------------------- ---------- ---------- -------- -------------------- ----------
2012-08-15 13:53:55  htzraw     HTZRAW          249 2012-08-15 14:29:31           1
2012-08-15 13:53:55  htzraw     HTZRAW          250 2012-08-15 14:34:14           1

  本文转自7343696 51CTO博客，原文链接：http://blog.51cto.com/luoping/964161，如需转载请自行联系原作者