Edit D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\sbpurge.sql
Rem Rem sbpurge.sql Rem Rem Copyright (c) 2000, 2007, Oracle. All rights reserved. Rem Rem NAME Rem sbpurge.sql - StandBy statspack PURGE Rem Rem DESCRIPTION Rem Purge a range of Snapshot Id's between the specified Rem begin and end Snap Id's Rem Rem NOTES Rem Should be run as standby statspack user, stdbyperf Rem Rem Rem MODIFIED (MM/DD/YY) Rem shsong 04/24/07 - fix bug Rem wlohwass 12/13/06 - Created Rem set feedback off verify off pages 999 whenever sqlerror exit rollback spool sbpurge.lis /* ------------------------------------------------------------------------- */ -- -- Get the current database/instance information - this will be used -- later in the report along with bid, eid to lookup snapshots column instt_num heading "Inst Num" format 99999; column instt_name heading "Instance" format a12; column dbb_name heading "DB Name" format a12; column dbbid heading "DB Id" format 9999999999 just c; column host heading "Host" format a12; prompt prompt prompt Instances in this Statspack schema prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ select distinct dbid dbbid , instance_number instt_num , db_name dbb_name , instance_name instt_name , host_name host from stats$database_instance dins where exists (select * from stats$standby_config scon where dins.instance_name = scon.inst_name); prompt prompt Using &&dbid for database Id prompt Using &&inst_num for instance number -- -- Set up the binds for dbid and instance_number and get inst_name column inst_name heading "INSTANCE NAME" new_value inst_name format a32; select distinct instance_name inst_name from stats$database_instance where instance_number = &&inst_num; variable dbid number; variable inst_num number; begin :dbid := &&dbid; :inst_num := &&inst_num; end; / -- -- List Snapshots column snap_id format 9999990 heading 'Snap Id' column snap_date format a21 heading 'Snapshot Started' just c column host_name format a15 heading 'Host' trunc column parallel format a3 heading 'OPS' trunc column level format 99 heading 'Snap|Level' column versn format a7 heading 'Release' column ucomment heading 'Comment' format a20; column baseline format a5 heading 'Base-|line?' prompt prompt prompt Snapshots for this database instance prompt ==================================== select s.snap_id , to_char(s.snap_time,' dd Mon YYYY HH24:mi:ss') snap_date , s.baseline , s.snap_level "level" , di.host_name host_name , s.ucomment from stats$snapshot s , stats$database_instance di where s.dbid = :dbid and di.dbid = :dbid and s.instance_number = :inst_num and di.instance_number = :inst_num and di.startup_time = s.startup_time order by db_name, instance_name, snap_id; -- -- Post warning prompt prompt prompt Warning prompt ~~~~~~~ prompt sppurge.sql deletes all snapshots ranging between the lower and prompt upper bound Snapshot Id's specified, for the database instance prompt you are connected to. Snapshots identified as Baseline snapshots prompt which lie within the snapshot range will not be purged. prompt prompt It is NOT possible to rollback changes once the purge begins. prompt prompt You may wish to export this data before continuing. prompt -- -- Obtain snapshot ranges prompt prompt Specify the Lo Snap Id and Hi Snap Id range to purge prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt Using &&LoSnapId for lower bound. prompt prompt Using &&HiSnapId for upper bound. -- -- Delete all data for the specified ranges prompt prompt Deleting snapshots &&losnapid - &&hisnapid.. variable lo_snap number; variable hi_snap number; variable snapshots_purged number; begin :lo_snap := &&losnapid; :hi_snap := &&hisnapid; :snapshots_purged := statspack_&&inst_name..purge( i_begin_snap => :lo_snap , i_end_snap => :hi_snap , i_snap_range => true , i_extended_purge => false , i_dbid => :dbid , i_instance_number => :inst_num); end; / -- -- set heading off select 'Number of Snapshots purged: ' || :snapshots_purged , '~~~~~~~~~~~~~~~~~~~~~~~~~~~' newline from sys.dual; set heading on prompt Purge of specified Snapshot range complete. prompt prompt -- -- spool off undefine dbid inst_num inst_name losnapid hisnapid set feedback on termout on whenever sqlerror continue
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de