下面的 script可以利用cron排程来自动执行清除超过保留数目的旧有snapshot资料，这个script不需要知道PERFSTAT此帐号的密码就可执行，并已经经由Oracle8.1.7和9.2.0上测试过。
步骤：
1)储存这个script取名为sp_purge.ksh在Unix主机上。
2)注重你的系统上tmp目录是否存在，假如你不想所有产生的log写到/tmp去，你必须去更改script。
3)假如你的oratab这个目录位置不是在/var/opt/oracle，你就必须手动去更新script来配合你的环境。
4)设定可执行权限给script： chmod u+x sp_purge.ksh
5)设定cron job来执行这个script。执行这个script需要三个参数：
要清除 snapshot的资料库名称。
要保留的 snapshot数量。
执行后要寄发电子邮件的对象。
00 19 * * 1-5 /scripts/sp_purge.ksh prod 60 mrogers@company.com /tmp/sp_purge_portal.log 2&1 &
这个范例是说：星期一到星期五天天晚上七点执行此 script，针对 'prod' 这个资料库只保留最近的60个snapshots纪录，多余的则清除，并且寄发讯息给 mrogers@company.com 。
6)注重这个 script应该配合指定的instance一起执行，假如这台主机上并没有这个script所指定的instance在执行中，一个简单的讯息可在tmp目录下找到。
*** ERROR: The ORACLE_SID specified in parameter 1 is not a valid SID.
(Note that the SID is case sensitive.)
7)所有产生的执行纪录都可以在/tmp下找到。
#!/bin/ksh
# Script Name: sp_purge.ksh
# This script is designed to purge StatsPack snapshots.
#
# Parameter $1 is the name of the database.
# Parameter $2 is the maximum number of snapshots to retain.
# Parameter $3 is the mail recipient for sUCcess messages.
#
# To succeed, this script must be run on the machine on which the
# instance is running.
# Example for calling this script:
#
# sp_purge.ksh prod 30 username@mycompany.com
# Script History:
#
# Who Date Action
# --------------- ------------ --------------------------------------------
# Mark J. Rogers 22-Sep-2003 Script creation.
#
#
#
tmp_dir=/tmp
# Validate the parameters.
if [[ $# -ne 3 ]]; then
echo ""
echo "*** ERROR: You must specify these parameters: "
echo ""
echo " 1: the name of the database"
echo " 2: the maximum # of snapshots to retain"
echo " 3: the mail recipient for success messages"
echo ""
exit 1
fi
grep "^${1}:" /var/opt/oracle/oratab /dev/null
if [[ $? -ne 0 ]]; then
echo ""
echo "*** ERROR: The ORACLE_SID specified in parameter 1 is not a valid SID."
echo " (Note that the SID is case sensitive.)"
echo ""
exit 1
fi
if [[ ! (${2} -ge 0) ]]; then
echo ""
echo "*** ERROR: Parameter 2 must specify the # of snapshots to retain."
echo ""
exit 1
fi
# Ensure that the instance is running on the current machine.
ps -ef grep pmon grep $1 /dev/null
if [[ $? -ne 0 ]]; then
echo ""
echo "*** ERROR: Instance $1 is not running on machine `uname -n` "
echo " on `date`."
echo " The instance must be running on the current machine for this"
echo " script to function properly."
echo ""
echo " Exiting..."
echo ""
exit 1
fi
# Establish error handling for this UNIX script.
function errtrap {
the_status=$?
echo ""
echo " *** ERROR: Error message $the_status occured on line number $1."
echo ""
echo " *** The script is aborting."
echo ""
exit $the_status
}
trap
'
errtrap $LINENO
'
ERR
# Set up the Oracle environment.
eXPort ORACLE_SID=${1}
export ORAENV_ASK=NO
. oraenv
script_name=${0##*/}
echo ""
echo "Script: $script_name"
echo " started on: `date`"
echo " by user: `id`"
echo " on machine: `uname -n`"
echo ""
echo "This script is designed to purge StatsPack snapshots for the "
echo " $ORACLE_SID database."
echo ""
echo "You have requested to retain no more than $2 StatsPack snapshots."
echo ""
tmp_script=${tmp_dir}/sp_purge_tmp_$ORACLE_SID.ksh # script to actually purge
tmp_output=${tmp_dir}/sp_purge_tmp_$ORACLE_SID.out # output to be mailed
rm -f $tmp_script
rm -f $tmp_output
sqlplus -s <<EOF_SP<br // as sysdba
whenever sqlerror exit failure rollback
whenever oserror exit failure rollback
SET SERVEROUTPUT ON
SET FEEDBACK OFF
VARIABLE P_SNAPS_TO_RETAIN NUMBER
VARIABLE P_LOSNAPID NUMBER
VARIABLE P_HISNAPID NUMBER
BEGIN
/* Assign values to these variables. */
:P_SNAPS_TO_RETAIN := ${2};
:P_LOSNAPID := -1;
:P_HISNAPID := -1;
END;
/
-- Identify the snapshot ids to purge, if any.
DECLARE
V_LOSNAPID NUMBER := NULL; -- Low snapshot ID to purge.
V_HISNAPID NUMBER := NULL; -- High snapshot ID to purge.
V_COUNT NUMBER := NULL; -- Number of snapshots current saved.
V_COUNTER NUMBER := 0; -- Temporary counter variable.
V_DBID NUMBER := NULL; -- Current database ID.
V_INSTANCE_NUMBER NUMBER := NULL; -- Current instance number.
V_SNAPS_TO_RETAIN NUMBER := :P_SNAPS_TO_RETAIN; -- Max snaps to retain.
BEGIN
select
d.dbid,
i.instance_number
INTO
v_DBID,
V_INSTANCE_NUMBER
from
v$database d,
v$instance i;
select
count(snap_id)
into
v_count
from
perfstat.stats$snapshot
where
dbid = V_DBID AND
instance_number = V_INSTANCE_NUMBER;
IF V_COUNT <= V_SNAPS_TO_RETAIN THEN
-- We do NOT need to perform a purge.
DBMS_OUTPUT.PUT_LINE ('NOTE: There are only '
to_char(v_count) ' snapshots currently saved.');
ELSE
-- We DO need to perform a purge.
DBMS_OUTPUT.PUT_LINE ('There are currently '
to_char(v_count) ' snapshots saved.');
-- OBTain the low snapshot id to be purged.
select
min(snap_id)
into
V_LOSNAPID
from
perfstat.stats$snapshot
where
dbid = V_DBID AND
instance_number = V_INSTANCE_NUMBER;
-- Obtain the high snapshot id to be purged.
FOR V_HISNAPID_REC IN
(SELECT
SNAP_ID
FROM
perfstat.stats$snapshot
WHERE
dbid = V_DBID AND
instance_number = V_INSTANCE_NUMBER
ORDER BY
SNAP_ID DESC)
LOOP
V_COUNTER := V_COUNTER + 1;
IF V_COUNTER V_SNAPS_TO_RETAIN THEN
V_HISNAPID := V_HISNAPID_REC.SNAP_ID;
EXIT; -- Exit this LOOP and proceed to the next statement.
END IF;
END LOOP;
:P_LOSNAPID := V_LOSNAPID;
:P_HISNAPID := V_HISNAPID;
END IF;
END;
/
prompt
-- Generate the specific purge script.
set linesize 60
spool $tmp_script
begin
IF (:P_LOSNAPID < -1) THEN
/* Build the script to purge the StatsPack snapshots. */
dbms_output.put_line('#!
/bin/ksh');
dbms_output.put_line('#THIS IS THE SCRIPT TO ACTUALLY PERFORM THE PURGE');
dbms_output.put_line('trap '' exit $? '' ERR');
dbms_output.put_line('sqlplus -s << SP_EOF2');
dbms_output.put_line('/ as sysdba');
dbms_output.put_line('whenever sqlerror exit failure rollback');
dbms_output.put_line('whenever oserror exit failure rollback');
dbms_output.put_line('@ $ORACLE_HOME/rdbms/admin/sppurge.sql');
dbms_output.put_line(:P_LOSNAPID);
dbms_output.put_line(:P_HISNAPID);
dbms_output.put_line('-- the following are needed again');
dbms_output.put_line('whenever sqlerror exit failure rollback');
dbms_output.put_line('whenever oserror exit failure rollback');
dbms_output.put_line('commit;');
dbms_output.put_line('exit');
dbms_output.put_line('SP_EOF2');
dbms_output.put_line('exit $?');
END IF;
end;
/
spool off
exit
EOF_SP
if [[ ! (-f ${tmp_script}) ]]; then
echo ""
echo "*** ERROR: Temporary script: ${tmp_script} does not exist."
echo ""
exit 1
fi
if [[ `cat ${tmp_script} wc -l` -ne 0 ]]; then
# Execute the newly generated StatsPack snapshot purge script.
chmod u+x $tmp_script
echo ""
echo "Performing the purge..."
echo ""
$tmp_script $tmp_output
cat $tmp_output # display the output
# Check the output file for a success message:
trap ' ' ERR # temporarily reset error handling for the grep command
grep "^Purge of specified Snapshot range complete." $tmp_output /dev/null
if [[ $? -ne 0 ]]; then
echo ""
echo "*** ERROR: The purge did not complete successfully."
echo " Check the log file $tmp_output."
echo ""
exit 1
fi
trap ' errtrap $LINENO ' ERR # re-establish desired error handler
else
# No purge script was created.
echo "No snapshot purge was necessary." $tmp_output
fi
echo ""
echo "The ${script_name} script appears to have completed "
echo " successfully on `date`."
echo ""
mailx
-s "sp_purge.ksh in $ORACLE_SID on `uname -n` completed successfully"
${3}
< $tmp_output
# End of script sp_purge.ksh.
