BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'AUTO_ORCHESTRATE_POST_REBUILD',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_pending NUMBER;
v_processing NUMBER;
v_failed NUMBER;
v_success NUMBER;
v_module VARCHAR2(48) := 'POST_REBUILD_ORCHESTRATOR';
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(v_module, 'Checking Driver State');
-- 1. Evaluate the current state of the rebuild driver
SELECT
NVL(SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END), 0),
NVL(SUM(CASE WHEN status = 'PROCESSING' THEN 1 ELSE 0 END), 0),
NVL(SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END), 0),
NVL(SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END), 0)
INTO v_pending, v_processing, v_failed, v_success
FROM rdsadm.elo_ln_rebuild_driver;
-- 2. Guardrails: If active or pending work remains, exit and sleep for 30 mins
IF v_pending > 0 OR v_processing > 0 THEN
DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
RETURN;
END IF;
-- 3. Guardrails: If failures exist, halt automation to prevent cascaded errors
IF v_failed > 0 THEN
DBMS_APPLICATION_INFO.SET_ACTION('HALTED: Failures detected in driver');
RETURN;
END IF;
-- 4. Execution: 100% Success confirmed. Kick off Stats.
IF v_success > 0 THEN
DBMS_APPLICATION_INFO.SET_ACTION('Executing Index Stats');
FOR idx IN (SELECT index_owner, index_name FROM rdsadm.elo_ln_rebuild_driver WHERE status = 'SUCCESS') LOOP
BEGIN
DBMS_STATS.GATHER_INDEX_STATS(
ownname => idx.index_owner,
indname => idx.index_name,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
degree => DBMS_STATS.AUTO_DEGREE,
no_invalidate => DBMS_STATS.AUTO_INVALIDATE,
force => TRUE
);
EXCEPTION WHEN OTHERS THEN
NULL; -- Isolate failures so the loop continues
END;
END LOOP;
-- Self-Terminate the Polling Job
DBMS_APPLICATION_INFO.SET_ACTION('Disabling Orchestrator');
DBMS_SCHEDULER.DISABLE('AUTO_ORCHESTRATE_POST_REBUILD');
END IF;
DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
END;]',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',
enabled => TRUE,
auto_drop => FALSE,
comments => 'Wakes up every 30 mins. Upon 100% clean rebuilds, executes index stats and self-terminates.'
);
END;
/
SELECT
job_name,
status,
TO_CHAR(actual_start_date, 'YYYY-MM-DD HH24:MI:SS') AS start_time,
TO_CHAR(log_date, 'YYYY-MM-DD HH24:MI:SS') AS end_time,
-- Formatted as a clean HH:MM:SS string for readability
LPAD(EXTRACT(DAY FROM run_duration) * 24 + EXTRACT(HOUR FROM run_duration), 2, '0') || ':' ||
LPAD(EXTRACT(MINUTE FROM run_duration), 2, '0') || ':' ||
LPAD(ROUND(EXTRACT(SECOND FROM run_duration)), 2, '0') AS hh_mm_ss_duration,
-- Calculated as a pure decimal for easy Excel logging and math (e.g., 2.75 hours)
ROUND(
(EXTRACT(DAY FROM run_duration) * 24) +
EXTRACT(HOUR FROM run_duration) +
(EXTRACT(MINUTE FROM run_duration) / 60) +
(EXTRACT(SECOND FROM run_duration) / 3600), 2
) AS logged_hours_decimal,
error# AS error_code
FROM
dba_scheduler_job_run_details
WHERE
owner = 'RDSADM' -- Or your specific schema
-- AND job_name LIKE '%IDX%' -- Uncomment to filter for specific jobs
ORDER BY
actual_start_date DESC
FETCH FIRST 50 ROWS ONLY;
1. Real-Time Execution Tracking
Because the script utilizes
DBMS_APPLICATION_INFO, you can watch exactly what the job is doing (e.g., "Checking Driver State" or "Executing Index Stats") in real-time by querying active sessions:SQL
SELECT
sid,
serial#,
module,
action,
status,
logon_time
FROM v$session
WHERE module = 'POST_REBUILD_ORCHESTRATOR';
2. Overall Job Status & Next Run Time
To verify the job is enabled, see what state it is in, and check exactly when it will wake up next, query the core scheduler jobs view:
SQL
SELECT
job_name,
enabled,
state,
run_count,
failure_count,
TO_CHAR(next_run_date, 'YYYY-MM-DD HH24:MI:SS') AS next_wakeup
FROM dba_scheduler_jobs
WHERE job_name = 'AUTO_ORCHESTRATE_POST_REBUILD';
(Note: Once the stats gathering finishes, the
enabled column will automatically flip to FALSE and state will become DISABLED.)3. Historical Run Logs
To review every 30-minute check it has performed, including how long it ran and if it encountered any Oracle errors, query the run details:
SQL
SELECT
status,
TO_CHAR(actual_start_date, 'MM-DD HH24:MI:SS') AS start_time,
-- Extracts exact duration string (HH:MM:SS)
LPAD(EXTRACT(HOUR FROM run_duration), 2, '0') || ':' ||
LPAD(EXTRACT(MINUTE FROM run_duration), 2, '0') || ':' ||
LPAD(ROUND(EXTRACT(SECOND FROM run_duration)), 2, '0') AS run_time,
additional_info
FROM dba_scheduler_job_run_details
WHERE job_name = 'AUTO_ORCHESTRATE_POST_REBUILD'
ORDER BY log_id DESC
FETCH FIRST 20 ROWS ONLY;
SELECT
TO_CHAR(actual_start_date, 'YYYY-MM-DD HH24:MI:SS') AS failed_at_time,
status,
error# AS oracle_error_code,
additional_info AS error_message
FROM dba_scheduler_job_run_details
WHERE job_name = 'AUTO_ORCHESTRATE_POST_REBUILD'
AND status = 'FAILED'
ORDER BY log_id DESC;