BC Clean sysaux xepdb1

-- Cronjob
-- # Clean SYSAUX
-- 30 1 * * 0  . /home/oracle/.bash_profile; cd /home/oracle/sql; sqlplus -s "/ as sysdba" @/home/oracle/sql/cleanup_sysaux_xepdb1.sql >/dev/null 2>&1

SET timing ON

SELECT to_char(sysdate, 'yyyy.mm.dd hh24:mi:ss') dt FROM dual;

spool clean_sysaux.log

prompt Cleanup started

ALTER SESSION SET container=xepdb1;

prompt DELETE expired tasks
EXEC prvt_advisor.delete_expired_tasks;

prompt Rebuild indices TO reclaim SPACE
EXEC dbms_pdb.exec_as_oracle_script('alter table WRI$_ADV_OBJECTS move');
ALTER INDEX WRI$_ADV_OBJECTS_PK rebuild;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 rebuild;

prompt Clean Job Scheduler
prompt Purge the ROWS FROM BOTH the job log AND the window log
DECLARE
    v_oldest INTEGER := 900;    -- days
    v_increment INTEGER := 1;
    v_cur_age INTEGER;
    v_min_age INTEGER := 3;     -- Retain 4 days
BEGIN
    v_cur_age := v_oldest;
    WHILE v_cur_age >= v_min_age LOOP
        dbms_scheduler.purge_log(v_cur_age, 'JOB_AND_WINDOW_LOG');
        commit;
        v_cur_age := v_cur_age - v_increment;
    END LOOP;
    dbms_scheduler.purge_log(v_min_age, 'JOB_AND_WINDOW_LOG');
    commit;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Execution halted with error number ' || sqlerrm);
END;
/

prompt CREATE a salvage TABLE TO store the ROWS they want TO keep
CREATE TABLE scheduler$_job_output_copy tablespace SYSAUX AS SELECT * FROM scheduler$_job_output;

prompt TRUNCATE TABLE scheduler$_job_output:
TRUNCATE TABLE scheduler$_job_output;

prompt Repopulate TABLE scheduler$_job_output:
INSERT INTO scheduler$_job_output SELECT * FROM scheduler$_job_output_copy;
commit;

prompt DROP the salvage TABLE:
DROP TABLE scheduler$_job_output_copy purge;
prompt Cleanup done.
spool off
exit