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;
/