1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
|
SET SERVEROUTPUT ON
DECLARE
var_owner DBA_RECYCLEBIN.OWNER%%type;
object_original DBA_RECYCLEBIN.original_name%%type;
nombre integer;
var_type varchar(30);
droptime DBA_RECYCLEBIN.droptime%%type;
CURSOR curseur IS select owner, original_name, count(object_name) Nombre, type, min(droptime)
from dba_recyclebin WHERE TO_DATE(DROPTIME,'YYYY-MM-DD HH24:MI:SS') < SYSDATE - INTERVAL '7' DAY
GROUP BY OWNER, ORIGINAL_NAME, TYPE
ORDER BY owner, type, 5 desc;
BEGIN
open curseur;
dbms_output.put_line('');
loop
fetch curseur into var_owner,
object_original,
nombre,
var_type,
droptime;
EXIT WHEN curseur%%notfound;
dbms_output.put_line(nombre || ' ' || var_type || ' : ' || var_owner || '.' || object_original || ' droppée au moins le ' || droptime);
end loop;
If curseur%%rowcount = 0 then
dbms_output.put_line('Toutes les corbeilles sont vides');
else
RAISE_APPLICATION_ERROR
(-20501, 'Trop de corbeilles pleines: ' || curseur%%ROWCOUNT);
end if;
close curseur;
END;
/ |
Partager