48 lines
1.5 KiB
SQL
48 lines
1.5 KiB
SQL
set serveroutput on
|
|
WHENEVER SQLERROR EXIT SQL.SQLCODE
|
|
|
|
-- For each dbid and instance_number combination
|
|
-- Set first snapshot for the day as baseline
|
|
DECLARE
|
|
l_sql_stmt VARCHAR2(4000);
|
|
BEGIN
|
|
FOR i IN (SELECT dbid, instance_number, trunc(snap_time), min(snap_id) as snap_id
|
|
FROM stats$snapshot a
|
|
GROUP BY dbid, instance_number, trunc(snap_time)
|
|
)
|
|
LOOP
|
|
l_sql_stmt := 'update stats$snapshot s
|
|
set s.baseline = ''Y''
|
|
where s.dbid = :1
|
|
and s.instance_number = :2
|
|
and s.snap_id = :3
|
|
AND nvl(s.baseline,''x'') != ''Y''
|
|
';
|
|
EXECUTE IMMEDIATE l_sql_stmt USING i.dbid, i.instance_number, i.snap_id;
|
|
END LOOP;
|
|
COMMIT;
|
|
END;
|
|
/
|
|
|
|
|
|
|
|
-- PURGE the data
|
|
DECLARE
|
|
l_sql_stmt VARCHAR2(4000);
|
|
BEGIN
|
|
FOR i in (select DISTINCT dbid, instance_number
|
|
FROM stats$database_instance)
|
|
LOOP
|
|
l_sql_stmt := 'BEGIN statspack.purge(i_dbid => :1 '
|
|
|| ' , i_instance_number => :2 '
|
|
|| ' , i_num_days => :3 '
|
|
-- || ' , i_extended_purge => :4 '
|
|
|| ' );
|
|
END; ';
|
|
DBMS_OUTPUT.PUT_LINE (l_sql_stmt);
|
|
EXECUTE IMMEDIATE l_sql_stmt USING i.dbid, i.instance_number, 120;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|