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