删除statspack快照的脚本

生产环境配置了statspack，每半小时进行一个快照收集，时间长了会积累很多的快照，需要定期删除，下面的脚本是保留两周的快照信息，之前的都删除

 

#!/usr/bin/sh

. /home/db/oracle/.profile
snap_count=`sqlplus -s perfstat/perfstat <        set feedback off;
        set pagesize 0;
        select count(*) from stats\\$snapshot;
EOF
`
if [ "${snap_count}" -gt "700" ]    ---- 保留700个快照，大概两周
        then
                max_snap=`sqlplus -s perfstat/perfstat << EOF
                set feedback off;
                set pagesize 0;
                select max(snap_id) from stats\\$snapshot;
                exit;
                EOF
                `
sqlplus -s perfstat/perfstat << EOF
        delete from stats\$snapshot where snap_id < ${max_snap} - 700;
        commit;
        exit;
EOF
fi
if [ $? != 0 ]
        then
        echo "`date +%y%m%d` Delete failed"
        else
        echo "`date +%y%m%d` Delete successful!"
fi