Vider automatiquement la corbeille RECYCLE_BIN

Le script SQL suivant active la corbeille de tables supprimées (RECYCLE_BIN), et crée respectivement :
- une table de log des vidages de la corbeille
- une procédure qui purge la corbeille avec une rétention définie
- un programme qui lance la procédure avec la rétention en argument
- un job qui lance le programme selon une fréquence définie

-- Tips : how to disable RECYCLEBIN, delete JOB and PROGRAM
-- Execute this script as SYSTEM
-- Note : need to close and restart session
-- ALTER SYSTEM SET recyclebin = OFF DEFERRED;
-- BEGIN
--     DBMS_SCHEDULER.DISABLE ('PERIODIC_PURGE_RECYCLE_BIN_JOB');
-- END;
-- /
-- BEGIN
--   DBMS_SCHEDULER.drop_job (job_name => 'PERIODIC_PURGE_RECYCLE_BIN_JOB');
-- END;
-- /
-- BEGIN
--     DBMS_SCHEDULER.DISABLE ('PERIODIC_PURGE_RECYCLE_BIN');
-- END;
-- /
-- BEGIN
--   DBMS_SCHEDULER.drop_program (program_name => 'PERIODIC_PURGE_RECYCLE_BIN');
-- END;
-- /

-- Enable recycle bin
ALTER SYSTEM SET recyclebin = ON DEFERRED;

-- Create JOB_LOG table if not exists
DECLARE
   table_exist number;
BEGIN
   select count(table_name) into table_exist from dba_tables
   where table_name='JOB_LOG';
   IF table_exist = 0 THEN
      EXECUTE IMMEDIATE 'CREATE TABLE SYSTEM.job_log (
        ts TIMESTAMP DEFAULT SYSTIMESTAMP,
        message VARCHAR2(255))';
   END IF;
END;
/

-- For verification
-- SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE TABLE_NAME = 'JOB_LOG';

-- Create procedure PURGE_RECYCLE_BIN
CREATE OR REPLACE PROCEDURE SYSTEM.PURGE_RECYCLE_BIN (v_nbjours IN INT) AUTHID CURRENT_USER AS
 TYPE cur_typ IS REF CURSOR;
 c cur_typ;
 v_cur_sql VARCHAR(200);
 v_owner VARCHAR(200);
 v_oname VARCHAR(200);
 v_sql VARCHAR(200);
BEGIN
  v_cur_sql := 'SELECT owner, original_name FROM sys.dba_recyclebin WHERE droptime < to_char(sysdate-'||v_nbjours||',''YYYY-MM-DD:HH24:MI:SS'') AND type=''TABLE''';
  OPEN c FOR v_cur_sql;
  LOOP
    FETCH c INTO v_owner, v_oname;
    EXIT WHEN c%NOTFOUND;
    v_sql := 'PURGE TABLE '||v_owner||'.'||v_oname;
    EXECUTE IMMEDIATE v_sql;
--    for debugging:
--    dbms_output.put_line('Table '||v_oname||' purged from RECYCLEBIN.');
    INSERT INTO job_log(message) VALUES ('Table '||v_oname||' purged from RECYCLEBIN.');
  END LOOP;
  CLOSE c;
END;
/

-- For verification
-- SELECT OWNER,PROCEDURE_NAME FROM DBA_PROCEDURES WHERE PROCEDURE_NAME LIKE '%RECYCLE%';

-- Create program+argument (default : v_nbjours=180)
BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM(
        PROGRAM_NAME => 'PERIODIC_PURGE_RECYCLE_BIN',
        PROGRAM_TYPE => 'STORED_PROCEDURE',
        PROGRAM_ACTION => 'SYSTEM.PURGE_RECYCLE_BIN',
        NUMBER_OF_ARGUMENTS => 1,
        ENABLED =>  FALSE,
        COMMENTS => 'PERIODIC_PURGE_RECYCLE_BIN'
    );
END;
/

BEGIN
    DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
        PROGRAM_NAME => 'PERIODIC_PURGE_RECYCLE_BIN',
        argument_name     => 'V_NBJOURS',
        argument_position => 1,
        argument_type     => 'INT',
        default_value     => '180'
    );
END;
/

BEGIN
    DBMS_SCHEDULER.ENABLE ('PERIODIC_PURGE_RECYCLE_BIN');
END;
/

-- For verification
-- SELECT PROGRAM_NAME,ENABLED FROM DBA_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME LIKE '%RECYCLE%';

-- Schedule and activate job with program
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'PERIODIC_PURGE_RECYCLE_BIN_JOB',
        start_date      =>  to_date('01/10/20 23:00:00', 'DD/MM/YY HH24:MI:SS'),
        end_date        => NULL,
        repeat_interval =>  'FREQ=DAILY',
        PROGRAM_NAME    => 'PERIODIC_PURGE_RECYCLE_BIN',
        ENABLED         => FALSE,
        COMMENTS        => 'PERIODIC_PURGE_RECYCLE_BIN_JOB'
    );
END;
/

BEGIN
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
        job_name => 'PERIODIC_PURGE_RECYCLE_BIN_JOB',
        argument_name => 'V_NBJOURS',
        argument_value => 1
    );
END;
/

BEGIN
    DBMS_SCHEDULER.ENABLE ('PERIODIC_PURGE_RECYCLE_BIN_JOB');
END;
/

-- For verification
-- SELECT JOB_NAME,PROGRAM_NAME,NUMBER_OF_ARGUMENTS,NEXT_RUN_DATE,ENABLED FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME LIKE '%RECYCLE%';

EXIT