74 lines
2.7 KiB
Plaintext
74 lines
2.7 KiB
Plaintext
create or replace PROCEDURE STDBYPERF.PURGE_ALL_INSTANCES(i_num_days IN NUMBER)
|
|
AS
|
|
BEGIN
|
|
FOR i IN (SELECT * FROM STATS$STANDBY_CONFIG)
|
|
LOOP
|
|
FOR J IN (SELECT *
|
|
FROM (SELECT i_num_days + level days
|
|
FROM dual
|
|
CONNECT BY LEVEL <= ( SELECT (TRUNC(sysdate) - TRUNC(MIN(s.SNAP_TIME)) ) - i_num_days
|
|
FROM STATS$SNAPSHOT s
|
|
WHERE s.db_unique_name = i.db_unique_name
|
|
AND s.instance_name = i.inst_name
|
|
group by s.db_unique_name , s.instance_name
|
|
)
|
|
ORDER BY level desc
|
|
)
|
|
)
|
|
LOOP
|
|
dbms_output.put_line('BEGIN ' || i.db_unique_name || '_' || i.inst_name || '.purge(' || j.days || ',FALSE,''' ||i.db_unique_name || ''',''' || i.inst_name || '''); END;' );
|
|
execute immediate 'BEGIN ' || i.db_unique_name || '_' || i.inst_name || '.purge(:num_days ,FALSE, :db_unique_name , :instance_name ); END;' using j.days , i.db_unique_name , i.inst_name;
|
|
END LOOP;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
create or replace PROCEDURE STDBYPERF.PURGE_ALL_INSTANCES(i_num_days IN NUMBER)
|
|
AS
|
|
l_snap_id NUMBER;
|
|
BEGIN
|
|
execute immediate 'alter session enable parallel DML';
|
|
execute immediate 'alter session enable parallel DDL';
|
|
FOR i IN (SELECT db_unique_name
|
|
, inst_name instance_name
|
|
FROM STATS$STANDBY_CONFIG
|
|
ORDER BY db_unique_name
|
|
, inst_name
|
|
)
|
|
LOOP
|
|
SELECT max(s.snap_id)
|
|
INTO l_snap_id
|
|
FROM STATS$SNAPSHOT s
|
|
where 1=1
|
|
and s.db_unique_name = i.db_unique_name
|
|
and s.instance_name = i.instance_name
|
|
and trunc(s.snap_time) < trunc(sysdate - i_num_days)
|
|
;
|
|
|
|
FOR j in (SELECT owner, table_name
|
|
FROM user_constraints
|
|
where owner = 'STDBYPERF'
|
|
and constraint_type = 'R'
|
|
and delete_rule = 'CASCADE'
|
|
and r_constraint_name = 'STATS$SNAPSHOT_PK'
|
|
and r_owner = 'STDBYPERF'
|
|
order by owner, table_name
|
|
)
|
|
LOOP
|
|
execute immediate 'delete /*+ parallel(a 16) */ from ' || j.owner || '.' || j.table_name || ' a '
|
|
|| ' WHERE a.db_unique_name = :db_unique_name '
|
|
|| ' AND a.instance_name = :instance_name '
|
|
|| ' AND a.snap_id < :snap_id ' USING i.db_unique_name , i.instance_name , l_snap_id ;
|
|
END LOOP;
|
|
execute immediate 'delete /*+ parallel(a 16) */ from STDBYPERF.STATS$SNAPSHOT a '
|
|
|| ' WHERE a.db_unique_name = :db_unique_name '
|
|
|| ' AND a.instance_name = :instance_name '
|
|
|| ' AND a.snap_id < :snap_id ' USING i.db_unique_name , i.instance_name , l_snap_id ;
|
|
COMMIT;
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
|
|
|