BC Clean sysaux - job queue

-- SYSAUX Tablespace Growing Due to SYS.SCHEDULER$_JOB_OUTPUT LOB Column (Doc ID 2095104.1)

SET LINES 200
SET pages 1000
COLUMN c1 heading "Owner"  format a15
COLUMN c2 heading "Name"   format a35
COLUMN c3 heading "Type"   format a15
COLUMN c4 heading "MB"      format 99999

SELECT
  owner c1,
  segment_name c2,
  segment_type c3,
  (bytes/1024/1024) c4
FROM dba_segments
WHERE tablespace_name='SYSAUX'
AND (bytes/1024/1024) > 50
ORDER BY c4 DESC;


SELECT *
FROM dba_lobs
WHERE segment_name = 'SYS_LOB0000010689C00003$$';


SELECT COUNT(*)
FROM SYS.SCHEDULER$_JOB_OUTPUT;

SELECT COUNT(*)
FROM sys.SCHEDULER$_JOB_RUN_DETAILS;


DESC SCHEDULER$_JOB_RUN_DETAILS;

EXEC dbms_scheduler.purge_log(0, 'JOB_AND_WINDOW_LOG');

-- http://webgeest.blogspot.com/2015/06/purge-loghistory.html

SELECT * FROM DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

EXEC DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','3');

EXEC DBMS_SCHEDULER.PURGE_LOG(3,which_log=>'JOB_LOG');

SELECT COUNT(1) FROM dba_scheduler_job_log WHERE log_date < sysdate-600;

EXEC dbms_scheduler.disable('PURGE_LOG');
EXEC dbms_scheduler.enable('PURGE_LOG');

EXEC DBMS_SCHEDULER.PURGE_LOG();


ALTER TABLE sys.scheduler$_event_log enable ROW movement;
ALTER TABLE sys.scheduler$_event_log shrink SPACE cascade;

ALTER TABLE sys.scheduler$_job_run_details enable ROW movement;
ALTER TABLE sys.scheduler$_job_run_details shrink SPACE cascade;

ALTER TABLE sys.scheduler$_event_log disable ROW movement;
ALTER TABLE sys.scheduler$_job_run_details disable ROW movement;


-- PDB version
EXEC dbms_pdb.exec_as_oracle_script('alter table sys.scheduler$_event_log enable row movement');
EXEC dbms_pdb.exec_as_oracle_script('alter table sys.scheduler$_event_log shrink space cascade');

EXEC dbms_pdb.exec_as_oracle_script('alter table sys.scheduler$_job_run_details enable row movement');
EXEC dbms_pdb.exec_as_oracle_script('alter table sys.scheduler$_job_run_details shrink space cascade');

EXEC dbms_pdb.exec_as_oracle_script('alter table sys.scheduler$_event_log disable row movement');
EXEC dbms_pdb.exec_as_oracle_script('alter table sys.scheduler$_job_run_details disable row movement');

-- To purge manually using the below command instead of Auto-Purge window:-

conn / AS sysdba;

EXEC prvt_advisor.delete_expired_tasks;

-- After the purge, do alter table WRI$_ADV_OBJECTS move; and rebuild three indexes. This will free space in the tablespace.

--SQL> alter table WRI$_ADV_OBJECTS move;
ALTER INDEX WRI$_ADV_OBJECTS_PK rebuild;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 rebuild;

-- PDB version

EXEC dbms_pdb.exec_as_oracle_script('alter index WRI$_ADV_OBJECTS_PK rebuild');
EXEC dbms_pdb.exec_as_oracle_script('alter index WRI$_ADV_OBJECTS_IDX_01 rebuild');