Ethan,
The following PL/SQL can be used. It's not tested, so
use at your own risk, YMMV, etc.... It also wasn't
written by me (of course anyone who knows me could
have told you that!) so don't bother asking me
questions about it. I imagine anyone proficient in
pl/sql could probably modify the sppurge.sql that
comes with 8.1.7 to do the same thing.
HTH,
-- Anita
The following SQL present a list of completed
snapshots, it will prompt for a starting and ending
snapshot id, and delete the appropriate rows:-
Rem
Rem statsdel.sql
Rem
Rem Copyright (c) Oracle Corporation 1999. All Rights
Reserved.
Rem
Rem NAME
Rem statsdel.sql
Rem
Rem DESCRIPTION
Rem SQL*Plus command file to remove old snapshot
values
Rem
Rem NOTES
Rem Usually run as the STATSPACK owner, PERFSTAT
Rem
Rem MODIFIED (MM/DD/YY)
Rem njohnsto.uk 06/13/00 - Created
Rem
clear break compute;
repfooter off;
ttitle off;
btitle off;
set timing off veri off space 1 flush on pause off
termout on numwidth 10;
set echo off feedback off pagesize 60 linesize 78
newpage 2;
--
-- Get the current database/instance information
column inst_num heading "Inst Num" new_value
inst_num format 99999;
column inst_name heading "Instance" new_value
inst_name format a10;
column db_name heading "DB Name" new_value db_name
format a10;
column dbid heading "DB Id" new_value dbid
format 9999999999 just c;
select d.dbid dbid
, d.name db_name
, i.instance_number inst_num
, i.instance_name inst_name
from v$database d,
v$instance i;
variable dbid number;
variable inst_num number;
variable inst_name varchar2(20);
variable db_name varchar2(20);
begin
:dbid := &dbid;
:inst_num := &inst_num;
:inst_name := '&inst_name';
:db_name := '&db_name';
end;
/
--
-- Ask for the starting and ending snapshot Ids to
delete between
set termout on;
column instart_fmt noprint;
column versn noprint heading 'Release' new_value
versn;
column host_name noprint heading 'Host' new_value
host_name;
column para noprint heading 'OPS' new_value
para;
column level heading 'Snap Level';
column snap_id heading 'SnapId' format 9990;
column snapdat heading 'Snap Started' just c
format a22;
break on inst_name on db_name on instart_fmt skip 1;
ttitle lef 'Completed Snapshots' skip 2;
select di.instance_name
inst_name
, di.host_name
host_name
, di.db_name
db_name
, sga.version
versn
, sga.parallel
para
, to_char(s.startup_time,' dd Mon "at"
HH24:mi:ss') instart_fmt
, s.snap_id
, to_char(s.snap_time,' dd Mon YYYY HH24:mi:ss')
snapdat
, s.snap_level
"level"
from stats$snapshot s
, stats$database_instance di
, stats$sgaxs sga
where s.dbid = :dbid
and di.dbid = :dbid
and sga.dbid = :dbid
and s.instance_number = :inst_num
and di.instance_number = :inst_num
and sga.instance_number = :inst_num
and sga.startup_time = s.startup_time
and sga.name = 'Database Buffers'
order by db_name, instance_name, snap_id;
clear break;
ttitle off;
accept bid number prompt "Enter first Snap Id to
delete: ";
accept eid number prompt "Enter last Snap Id to
delete: ";
set termout off;
variable bid number;
variable eid number;
variable versn varchar2(10);
variable para varchar2(9);
variable host_name varchar2(64);
declare
min_snap_id number;
max_snap_id number;
prev_snap_id number;
next_snap_id number;
prev_start_time date;
next_start_time date;
begin
--
-- set up parameters
--
:bid := &bid;
:eid := &eid;
:versn := '&versn';
:para := '¶';
:host_name := '&host_name';
:dbid := &dbid;
:inst_num := &inst_num;
:inst_name := '&inst_name';
:db_name := '&db_name';
--
-- check if values are valid
if :eid > :bid then
--
-- find the min and max snap_ids
select max(snap_id),min(snap_id)
into max_snap_id,min_snap_id
from stats$snapshot s
where s.dbid = :dbid
and s.instance_number = :inst_num;
--
-- find the startup time of the previous record
-- if we've selected the first record, use
1-Jan-1900
if :bid-1 <min_snap_id then
prev_start_time :=
to_date('01-JAN-1900','DD-MON-YYYY');
prev_snap_id:=0;
else
select snap_id,startup_time
into prev_snap_id,prev_start_time
from stats$snapshot s
where snap_id = (
select max(snap_id) from
stats$snapshot t
where snap_id <:bid
and t.dbid = :dbid
and t.instance_number =
:inst_num)
and s.dbid = :dbid
and s.instance_number = :inst_num;
end if;
--
-- find the startup time of the next record
-- if we've selected the last record, use SYSDATE
if :eid+1 > max_snap_id then
next_start_time := sysdate;
next_snap_id :=0;
else
select snap_id, startup_time
into next_snap_id, next_start_time
from stats$snapshot s
where snap_id = (
select min(snap_id) from
stats$snapshot t
where snap_id >:eid
and t.dbid = :dbid
and t.instance_number =
:inst_num)
and s.dbid = :dbid
and s.instance_number = :inst_num;
end if;
--
-- update sgaxs information to reflect the
remaining snapshots
update stats$sgaxs
set snap_id=next_snap_id
where startup_time=next_start_time
and prev_start_time < startup_time
and dbid = :dbid
and instance_number = :inst_num;
--
-- delete the snapshots - no need to attend to any
of the other tables
-- as the foreign key should have a cascade delete
status
delete from stats$snapshot s
where snap_id between :bid and :eid
and s.dbid = :dbid
and s.instance_number = :inst_num;
--
commit;
end if;
end;
/
set termout on
break on inst_name on db_name on instart_fmt skip 1;
ttitle lef 'Completed Snapshots' skip 2;
select di.instance_name
inst_name
, di.host_name
host_name
, di.db_name
db_name
, sga.version
versn
, sga.parallel
para
, to_char(s.startup_time,' dd Mon "at"
HH24:mi:ss') instart_fmt
, s.snap_id
, to_char(s.snap_time,' dd Mon YYYY HH24:mi:ss')
snapdat
, s.snap_level
"level"
from stats$snapshot s
, stats$database_instance di
, stats$sgaxs sga
where s.dbid = :dbid
and di.dbid = :dbid
and sga.dbid = :dbid
and s.instance_number = :inst_num
and di.instance_number = :inst_num
and sga.instance_number = :inst_num
and sga.startup_time = s.startup_time
and sga.name = 'Database Buffers'
order by db_name, instance_name, snap_id;
clear break;
clear columns sql
ttitle off;
btitle off;
repfooter off;
set linesize 78 termout on feedback 6;
--
-- End of script file;
--- "Post, Ethan" <[EMAIL PROTECTED]> wrote:
> Anyone found a more efficient way to remove a
> statspack snapshot prior to
> 8.17?
>
>
> ***METALINK SAYS***
>
> 7) How do I remove StatsPack snapshots that I am no
> longer interested in?
>
> This functionality is available in Oracle
> 8.1.7 using a script called
>
> sppurge.sql located in
> $ORACLE_HOME/rdbms/admin.
>
> In Oracle 8.1.6 you will need to contact
> Oracle Support for
> assistance. **YOU GOT TO BE KIDDING**
>
> Thanks,
> - Ethan Post
> - http://www.geocities.com/epost1
__________________________________________________
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger.
http://im.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: A. Bardeen
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
