下面这个脚本是实现在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
#!/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，如需转载请自行联系原作者
