Накидал тут скриптик для крона. Корзина, как я помню, очищается при autoextend или полностью, а этот скриптик очищает корзину от объектов, которые туда попали более чем 7 дней назад. Ставлю на воскресенье в крон и вперед...
SQL код:
SET SERVEROUT ON
SET LINES 200
SET PAGES 1000
DECLARE
v_purge_date DATE := SYSDATE - 7;
v_sql VARCHAR2(512);
v_count NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('Purge before ' || TO_CHAR(v_purge_date, 'DD.MM.YYYY HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
SELECT COUNT(*) INTO v_count
FROM dba_recyclebin
WHERE to_date(droptime, 'YYYY-MM-DD:HH24:MI:SS') < v_purge_date;
DBMS_OUTPUT.PUT_LINE('Ready to purge : ' || v_count);
IF v_count > 0 THEN
FOR tab_rec IN (SELECT owner, original_name, object_name
FROM dba_recyclebin
WHERE to_date(droptime, 'YYYY-MM-DD:HH24:MI:SS') < v_purge_date
AND type = 'TABLE') LOOP
BEGIN
v_sql := 'PURGE TABLE "' || tab_rec.owner || '"."' || tab_rec.object_name || '"';
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Table purged : ' || tab_rec.owner || '.' || tab_rec.original_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Table purge error ' || tab_rec.owner || '.' ||
tab_rec.original_name || ': ' || SQLERRM);
END;
END LOOP;
FOR idx_rec IN (SELECT owner, original_name, object_name
FROM dba_recyclebin
WHERE to_date(droptime, 'YYYY-MM-DD:HH24:MI:SS') < v_purge_date
AND type = 'INDEX') LOOP
BEGIN
v_sql := 'PURGE INDEX "' || idx_rec.owner || '"."' || idx_rec.object_name || '"';
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Index purged : ' || idx_rec.owner || '.' || idx_rec.original_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Index purge error ' || idx_rec.owner || '.' ||
idx_rec.original_name || ': ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Succesfully purged : ' || v_count);
ELSE
DBMS_OUTPUT.PUT_LINE('Nothing to purge.');
END IF;
END;
/