99 lines
3.6 KiB
SQL
99 lines
3.6 KiB
SQL
-- +----------------------------------------------------------------------------+
|
|
-- | Jeffrey M. Hunter |
|
|
-- | jhunter@idevelopment.info |
|
|
-- | www.idevelopment.info |
|
|
-- |----------------------------------------------------------------------------|
|
|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
|
|
-- |----------------------------------------------------------------------------|
|
|
-- | DATABASE : Oracle |
|
|
-- | FILE : sp_purge_30_days_10g.sql |
|
|
-- | CLASS : Statspack |
|
|
-- | PURPOSE : This script is responsible for removing all Statspack snapshot |
|
|
-- | records older than 30 days. Most of the code contained in this |
|
|
-- | script is modeled after the Oracle supplied sppurge.sql script |
|
|
-- | but removes by Snapshot date rather than Snapshot IDs. |
|
|
-- | |
|
|
-- | Note that this script only works with Oracle10g. |
|
|
-- | |
|
|
-- | NOTE : As with any code, ensure to test this script in a development |
|
|
-- | environment before attempting to run it in production. |
|
|
-- +----------------------------------------------------------------------------+
|
|
|
|
SET LINESIZE 145
|
|
SET PAGESIZE 9999
|
|
SET FEEDBACK off
|
|
SET VERIFY off
|
|
|
|
DEFINE days_to_keep=30
|
|
|
|
UNDEFINE dbid inst_num snapshots_purged
|
|
|
|
WHENEVER SQLERROR EXIT ROLLBACK
|
|
|
|
SPOOL sp_purge_&days_to_keep._days_10g.lis
|
|
|
|
|
|
prompt
|
|
prompt
|
|
prompt
|
|
prompt +----------------------------------------------------------------------------+
|
|
prompt | Get database and instance currently connected to. |
|
|
prompt +----------------------------------------------------------------------------+
|
|
|
|
SET FEEDBACK off
|
|
|
|
COLUMN inst_num FORMAT 99999999999999 HEADING "Instance Num." NEW_VALUE inst_num
|
|
COLUMN inst_name FORMAT a15 HEADING "Instance Name" NEW_VALUE inst_name
|
|
COLUMN db_name FORMAT a10 HEADING "DB Name" NEW_VALUE db_name
|
|
COLUMN dbid FORMAT 9999999999 HEADING "DB Id" NEW_VALUE dbid
|
|
|
|
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);
|
|
VARIABLE num_days NUMBER;
|
|
VARIABLE snapshots_purged NUMBER;
|
|
|
|
BEGIN
|
|
:dbid := &dbid;
|
|
:inst_num := &inst_num;
|
|
:inst_name := '&inst_name';
|
|
:db_name := '&db_name';
|
|
:num_days := &days_to_keep;
|
|
|
|
:snapshots_purged := statspack.purge( i_num_days => :num_days
|
|
, i_extended_purge => true
|
|
, i_dbid => :dbid
|
|
, i_instance_number => :inst_num);
|
|
END;
|
|
/
|
|
|
|
SET FEEDBACK on
|
|
|
|
prompt
|
|
prompt
|
|
prompt
|
|
prompt +----------------------------------------------------------------------------+
|
|
prompt | Removed Statspack snapshots older than &days_to_keep days. |
|
|
prompt +----------------------------------------------------------------------------+
|
|
|
|
print snapshots_purged
|
|
|
|
SPOOL off
|
|
|
|
COMMIT;
|
|
|
|
EXIT
|
|
|