Idempotent DDL: Safe to run repeatedly.
/********************************************************************
NUCLEAR CLEAN & REBUILD stats_job_log
→ Drops everything if exists
→ Recreates table, identity column starting at 1
→ Recreates index + view
→ 100% safe, re-runnable forever
********************************************************************/
BEGIN
-- 1. Drop the table if it exists (including all data, constraints, triggers)
EXECUTE IMMEDIATE 'DROP TABLE stats_job_log PURGE';
DBMS_OUTPUT.PUT_LINE('Old stats_job_log table dropped (if existed).');
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN RAISE; END IF; -- -942 = table does not exist → fine
DBMS_OUTPUT.PUT_LINE('No existing table to drop.');
END;
/
-- 2. Create fresh table with identity starting at 1
EXECUTE IMMEDIATE q'[
CREATE TABLE stats_job_log (
log_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
job_name VARCHAR2(128),
log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
message VARCHAR2(4000),
CONSTRAINT pk_stats_job_log PRIMARY KEY (log_id)
)
]';
DBMS_OUTPUT.PUT_LINE('Fresh stats_job_log table created with identity starting at 1');
-- 3. Index (fast log tailing)
BEGIN
EXECUTE IMMEDIATE '
CREATE INDEX ix_stats_job_log_time
ON stats_job_log(log_time DESC, job_name)';
DBMS_OUTPUT.PUT_LINE('Index created');
EXCEPTION WHEN OTHERS THEN
IF SQLCODE != -955 THEN RAISE; END IF; -- index already exists (should never happen)
END;
/
-- 4. Friendly view
CREATE OR REPLACE VIEW v_stats_gather_log AS
SELECT * FROM stats_job_log ORDER BY log_id DESC;
DBMS_OUTPUT.PUT_LINE('View v_stats_gather_log created');
-- 5. Confirm it starts at 1
DECLARE
v_next NUMBER;
BEGIN
EXECUTE IMMEDIATE '
SELECT stats_job_log_seq.NEXTVAL FROM dual'
INTO v_next
FROM dual; -- dummy, just to force sequence creation if needed
DBMS_OUTPUT.PUT_LINE('Identity sequence ready – next log_id will be 1');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
DBMS_OUTPUT.PUT_LINE('NUCLEAR REBUILD COMPLETE – table is pristine and starts at ID=1');
2. The Master Script (Run Anywhere)
Fixed the local function scope issue.
DECLARE
-- CONFIGURATION SECTION
v_owner VARCHAR2(128) := 'YOUR_SCHEMA'; -- <== CHANGE THIS
v_table_list VARCHAR2(4000) := 'FACT_SALES,FACT_ORDERS,DIM_CUSTOMER'; -- <== CHANGE THIS
v_job_name VARCHAR2(128) := 'GATHER_STATS_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS');
v_plsql CLOB;
BEGIN
-- Dynamic PL/SQL Block
v_plsql := q'[
DECLARE
v_job VARCHAR2(128) := ']' || v_job_name || q'[';
-- Autonomous logger: Writes to table even if stats fail
PROCEDURE log(p_msg VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO stats_job_log(job_name, message)
VALUES (v_job, SUBSTR(p_msg,1,4000));
COMMIT;
END;
BEGIN
log('STARTED – Job: ' || v_job);
-- Loop through tables using pure SQL parsing (Scope Safe)
FOR rec IN (
SELECT t.table_name
FROM all_tables t
WHERE t.owner = ']' || v_owner || q'['
AND t.table_name IN (
SELECT TRIM(REGEXP_SUBSTR(']' || v_table_list || q'[', '[^,]+', 1, LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(']' || v_table_list || q'[', '[^,]+', 1, LEVEL) IS NOT NULL
)
)
LOOP
log('Processing ' || rec.table_name || ' ...');
BEGIN
-- Industry Standard Stats Gathering
DBMS_STATS.GATHER_TABLE_STATS(
ownname => ']' || v_owner || q'[',
tabname => rec.table_name,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- Gold Standard
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => DBMS_STATS.AUTO_DEGREE, -- Load Aware
cascade => TRUE,
granularity => 'AUTO',
no_invalidate => DBMS_STATS.AUTO_INVALIDATE,
force => TRUE
);
log('SUCCESS → ' || rec.table_name);
EXCEPTION WHEN OTHERS THEN
-- Catch error, log it, but CONTINUE to next table
log('FAILED → ' || rec.table_name || ': ' || SQLERRM);
END;
END LOOP;
log('COMPLETED – Run Finished');
END;]';
-- Cleanup old job if exists (Idempotency)
BEGIN DBMS_SCHEDULER.DROP_JOB(v_job_name, force => TRUE); EXCEPTION WHEN OTHERS THEN NULL; END;
-- Submit Background Job
DBMS_SCHEDULER.CREATE_JOB(
job_name => v_job_name,
job_type => 'PLSQL_BLOCK',
job_action => v_plsql,
enabled => TRUE,
auto_drop => TRUE,
comments => 'Bullet-proof stats gather (No APEX)'
);
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Job Submitted: ' || v_job_name);
DBMS_OUTPUT.PUT_LINE('Monitor Live: SELECT * FROM v_stats_gather_log WHERE ROWNUM <= 20;');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
END;
/
-- FINAL VERSION – I dare Oracle to complain again
SELECT
j.job_name,
j.enabled,
j.state AS job_state,
TO_CHAR(j.last_start_date, 'YYYY-MM-DD HH24:MI') AS last_started,
TO_CHAR(j.next_run_date, 'YYYY-MM-DD HH24:MI') AS next_scheduled,
d.status AS last_run_status,
d.last_run_time,
d.duration_mins AS last_run_minutes,
d.error_code,
CASE
WHEN d.status = 'SUCCEEDED' THEN 'OK'
WHEN d.status = 'FAILED' THEN 'ERROR'
WHEN d.status IS NULL THEN 'Never run'
ELSE d.status
END AS health,
ROUND(SYSDATE - NVL(d.last_success_date, DATE '1900-01-01'), 1)
AS days_since_last_success,
j.run_count,
j.failure_count
FROM dba_scheduler_jobs j
LEFT JOIN (
------------------------------------------------------------------
-- Latest run + safe duration + last successful run date
------------------------------------------------------------------
SELECT
job_name,
status,
error# AS error_code,
TO_CHAR(actual_start_date, 'YYYY-MM-DD HH24:MI:SS') AS last_run_time,
-- THIS LINE IS THE ONLY ONE THAT WORKS EVERYWHERE
ROUND(
(CAST(log_date AS DATE) -
CAST(actual_start_date AS DATE)) * 1440
, 2) AS duration_mins,
-- Date of the most recent successful run (for "days since" column)
MAX(CASE WHEN status = 'SUCCEEDED' THEN actual_start_date END)
OVER (PARTITION BY job_name) AS last_success_date,
ROW_NUMBER() OVER (PARTITION BY job_name
ORDER BY actual_start_date DESC) AS rn
FROM dba_scheduler_job_run_details
) d ON j.job_name = d.job_name AND d.rn = 1
WHERE j.job_name LIKE 'GATHER_STATS_%'
ORDER BY days_since_last_success DESC NULLS LAST, j.job_name;
1. The Fixed "Smart Summary" (Run this now)
This version explicitly checks for the STOPPED status so you see "KILLED" in big letters.
SQLSELECT
job_name,
TO_CHAR(actual_start_date, 'YYYY-MM-DD HH24:MI:SS') AS started_at,
-- STATUS LOGIC: explicitly check for STOPPED
CASE
WHEN status = 'STOPPED' THEN 'KILLED' -- <== The fix
WHEN status = 'FAILED' THEN 'FAILED'
WHEN status = 'SUCCEEDED' THEN 'SUCCESS'
WHEN status IS NULL THEN '🏃 RUNNING' -- If NULL, it's still active
ELSE status
END AS current_status,
-- DURATION:
-- If Running: Calc from NOW.
-- If Stopped/Done: Calc from Log Date.
ROUND(
(CAST(log_date AS DATE) - CAST(actual_start_date AS DATE)) * 1440
, 2) AS duration_mins,
error# AS error_code,
additional_info
FROM dba_scheduler_job_run_details
WHERE job_name LIKE 'GATHER_STATS_%'
ORDER BY actual_start_date DESC
FETCH FIRST 20 ROWS ONLY;
2. If you are using your Custom Log Table (stats_job_log)
Your custom log table doesn't know the job was killed by the scheduler (because the script stopped running before it could write "Failed").
To fix this in the custom log view, we cross-reference it with the scheduler history. Run this to see the truth:
SQLSELECT
l.job_name,
TO_CHAR(MIN(l.log_time), 'HH24:MI:SS') as start_time,
-- The Truth Serum: Check what actually happened to the Scheduler Job
COALESCE(
(SELECT CASE
WHEN status = 'STOPPED' THEN 'KILLED MANUALLY'
WHEN status = 'FAILED' THEN 'FAILED'
WHEN status = 'SUCCEEDED' THEN 'SUCCESS'
END
FROM dba_scheduler_job_run_details d
WHERE d.job_name = l.job_name),
'🏃 RUNNING'
) as real_status,
-- Show the last message (likely "Processing Table X...")
SUBSTR(MAX(l.message) KEEP (DENSE_RANK LAST ORDER BY l.log_id), 1, 60) as last_log_entry
FROM stats_job_log l
GROUP BY l.job_name
ORDER BY MIN(l.log_time) DESC;
3. Monitoring (The Control Room)
Use these to track your job.
The "Live Tail" (Watch it happen)
SELECT TO_CHAR(log_time,'HH24:MI:SS') || ' | ' || message AS live_log
FROM v_stats_gather_log
WHERE job_name = (SELECT MAX(job_name) FROM stats_job_log)
ORDER BY log_id DESC;
The "Executive Summary" (Pass/Fail count)
SELECT
job_name,
COUNT(CASE WHEN message LIKE 'SUCCESS%' THEN 1 END) AS tables_analyzed,
COUNT(CASE WHEN message LIKE 'FAILED%' THEN 1 END) AS tables_failed,
ROUND((MAX(log_time) - MIN(log_time)) * 1440, 2) AS duration_mins
FROM stats_job_log
WHERE job_name = (SELECT MAX(job_name) FROM stats_job_log)
GROUP BY job_name;
ere are the best "Industry Standard" queries to utilize them.
1. DBA_SCHEDULER_JOBS (The Configuration)
Use this to check "Is my job set up correctly and scheduled to run?" It tells you if the job exists, if it is enabled, and when it will fire next.
SQLSELECT
job_name,
enabled,
state, -- 'SCHEDULED', 'RUNNING', 'DISABLED'
job_action, -- The actual code/script it runs
last_start_date, -- When it last started
next_run_date, -- When it will run next (Critical for recurring jobs)
failure_count, -- How many times it failed in a row
retry_count
FROM dba_scheduler_jobs
WHERE job_name LIKE 'GATHER_STATS_%'
ORDER BY job_name;
2. DBA_SCHEDULER_JOB_RUN_DETAILS (The History)
Use this to check "Did it work yesterday, and how long did it take?" It logs every single execution.
SQLSELECT
log_id,
job_name,
status, -- 'SUCCEEDED', 'FAILED', 'STOPPED'
TO_CHAR(actual_start_date, 'DD-MON HH24:MI') AS start_time,
-- Calculate precise duration in Minutes
TRUNC(
EXTRACT(HOUR FROM run_duration) * 60 +
EXTRACT(MINUTE FROM run_duration) +
EXTRACT(SECOND FROM run_duration) / 60, 2
) AS duration_mins,
output, -- Any standard output (rarely used if using custom logs)
error#, -- Oracle Error Code (e.g., 1652)
additional_info -- Crucial for debugging failures
FROM dba_scheduler_job_run_details
WHERE job_name LIKE 'GATHER_STATS_%'
ORDER BY actual_start_date DESC;
3. The "Master View" (Join Query)
Pro Tip: This is the most useful query for a DBA. It joins the two views to show you the Job Definition alongside its Most Recent Status.
SQLSELECT
j.job_name,
j.enabled,
j.state,
j.next_run_date,
d.status AS last_run_status,
d.duration_mins AS last_run_duration,
d.actual_start_date AS last_run_time
FROM dba_scheduler_jobs j
LEFT JOIN (
-- Get only the most recent run for each job
SELECT job_name, status, actual_start_date,
TRUNC(EXTRACT(MINUTE FROM run_duration) + EXTRACT(SECOND FROM run_duration)/60, 2) as duration_mins,
ROW_NUMBER() OVER (PARTITION BY job_name ORDER BY actual_start_date DESC) as rn
FROM dba_scheduler_job_run_details
) d ON j.job_name = d.job_name AND d.rn = 1
WHERE j.job_name LIKE 'GATHER_STATS_%';
Key Takeaway:
If you create a job with auto_drop => TRUE (like in our script), it disappears from dba_scheduler_jobs the moment it finishes.
However, its history remains in dba_scheduler_job_run_details so you can verify it ran.
-- 3.3 DBA_SCHEDULER_JOBS – current job definition (100% safe columns)
SELECT
job_name,
enabled,
state,
last_start_date,
next_run_date,
run_count,
failure_count,
retry_count
FROM dba_scheduler_jobs
WHERE job_name LIKE 'GATHER_STATS_%'
ORDER BY job_name;
-- 3.4 DBA_SCHEDULER_JOB_RUN_DETAILS – execution history (100% safe columns)
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,
ROUND((log_date - actual_start_date) * 1440, 2) AS duration_minutes,
error#,
additional_info
FROM dba_scheduler_job_run_details
WHERE job_name LIKE 'GATHER_STATS_%'
ORDER BY actual_start_date DESC;
-- 3.5 Master view – latest run + job status in one shot
SELECT
j.job_name,
j.enabled,
j.state,
j.next_run_date,
d.status AS last_run_status,
d.duration_minutes AS last_run_mins,
d.start_time AS last_run_time
FROM dba_scheduler_jobs j
LEFT JOIN (
SELECT job_name, status,
ROUND((log_date - actual_start_date)*1440,2) AS duration_minutes,
TO_CHAR(actual_start_date,'YYYY-MM-DD HH24:MI:SS') AS start_time,
ROW_NUMBER() OVER (PARTITION BY job_name ORDER BY actual_start_date DESC) rn
FROM dba_scheduler_job_run_details
) d ON j.job_name = d.job_name AND d.rn = 1
WHERE j.job_name LIKE 'GATHER_STATS_%';
-- 3.6 Is a stats job running right now?
SELECT 'RUNNING → ' || job_name || ' for ' ||
ROUND((SYSTIMESTAMP - actual_start_date)*1440,1) || ' minutes' AS status
FROM dba_scheduler_job_run_details
WHERE status = 'RUNNING' AND job_name LIKE 'GATHER_STATS_%'
UNION ALL
SELECT 'No stats job currently running'
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM dba_scheduler_job_run_details
WHERE status = 'RUNNING' AND job_name LIKE 'GATHER_STATS_%');
fixed
/********************************************************************
FINAL CANVAS – 100% ORA-00932-PROOF DURATION CALCULATIONS
Works on 11g / 12c / 19c / 21c / 23ai / RDS / everywhere
********************************************************************/
------------------------------------------------------------------
-- 1. JOB HISTORY – perfect duration, no interval errors ever
------------------------------------------------------------------
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,
ROUND( (CAST(log_date AS DATE) -
CAST(actual_start_date AS DATE)) * 1440, 2) AS duration_minutes,
error#,
SUBSTR(additional_info,1,300) AS info
FROM dba_scheduler_job_run_details
WHERE job_name LIKE 'GATHER_STATS_%'
ORDER BY actual_start_date DESC;
------------------------------------------------------------------
-- 2. IS ANYTHING RUNNING RIGHT NOW? – live elapsed time
------------------------------------------------------------------
SELECT
'RUNNING → ' || job_name ||
' for ' ||
ROUND( (CAST(SYSTIMESTAMP AS DATE) - CAST(actual_start_date AS DATE)) * 1440, 1) || ' minutes' AS live_status
FROM dba_scheduler_job_run_details
WHERE status = 'RUNNING'
AND job_name LIKE 'GATHER_STATS_%'
UNION ALL
SELECT 'No stats job currently running'
FROM dual
WHERE NOT EXISTS (
SELECT 1 FROM dba_scheduler_job_run_details
WHERE status = 'RUNNING' AND job_name LIKE 'GATHER_STATS_%'
);
------------------------------------------------------------------
-- 3. EXECUTIVE SUMMARY – last 20 runs, clean and fast
------------------------------------------------------------------
SELECT
job_name,
TO_CHAR(actual_start_date,'YYYY-MM-DD HH24:MI:SS') AS started,
status,
ROUND( (CAST(log_date AS DATE) - CAST(actual_start_date AS DATE)) * 1440, 1) AS minutes,
error#
FROM dba_scheduler_job_run_details
WHERE job_name LIKE 'GATHER_STATS_%'
ORDER BY actual_start_date DESC
FETCH FIRST 20 ROWS ONLY;
------------------------------------------------------------------
-- 4. MASTER VIEW – current job state + most recent run result
------------------------------------------------------------------
SELECT
j.job_name,
j.enabled,
j.state,
j.next_run_date,
d.status AS last_run_status,
d.duration_minutes AS last_run_mins,
d.started
FROM dba_scheduler_jobs j
LEFT JOIN (
SELECT
job_name,
status,
TO_CHAR(actual_start_date,'YYYY-MM-DD HH24:MI:SS') AS started,
ROUND( (CAST(log_date AS DATE) - CAST(actual_start_date AS DATE)) * 1440, 1) AS duration_minutes,
ROW_NUMBER() OVER (PARTITION BY job_name ORDER BY actual_start_date DESC) rn
FROM dba_scheduler_job_run_details
) d ON j.job_name = d.job_name AND d.rn = 1
WHERE j.job_name LIKE 'GATHER_STATS_%'
ORDER BY j.job_name;
------------------------------------------------------------------
-- 5. BONUS: stats_job_log duration summary (your custom log)
------------------------------------------------------------------
SELECT
job_name,
TO_CHAR(MIN(log_time),'YYYY-MM-DD HH24:MI:SS') AS started,
TO_CHAR(MAX(log_time),'YYYY-MM-DD HH24:MI:SS') AS finished,
ROUND((MAX(log_time) - MIN(log_time)) * 1440, 2) AS duration_minutes,
COUNT(CASE WHEN message LIKE 'SUCCESS%' THEN 1 END) AS success,
COUNT(CASE WHEN message LIKE 'FAILED%' THEN 1 END) AS failed
FROM stats_job_log
GROUP BY job_name
ORDER BY MIN(log_time) DESC;
more enhancement
/********************************************************************
FINAL CANVAS – 100% ORA-00932-PROOF & VERSION SAFE
Works on 11g / 12c / 19c / 21c / 23ai / RDS / everywhere
********************************************************************/
------------------------------------------------------------------
-- 1. JOB HISTORY – Perfect duration, safety cast applied
------------------------------------------------------------------
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,
-- FIX: Cast both to DATE so result is NUMBER (Days)
ROUND( (CAST(log_date AS DATE) - CAST(actual_start_date AS DATE)) * 1440, 2) AS duration_minutes,
error#,
SUBSTR(additional_info,1,300) AS info
FROM dba_scheduler_job_run_details
WHERE job_name LIKE 'GATHER_STATS_%'
ORDER BY actual_start_date DESC;
------------------------------------------------------------------
-- 2. IS ANYTHING RUNNING RIGHT NOW? – Using correct view
------------------------------------------------------------------
-- FIX: Query dba_scheduler_jobs (Live Status) instead of run_details (History)
SELECT
'RUNNING → ' || job_name ||
' for ' ||
ROUND( (SYSDATE - CAST(last_start_date AS DATE)) * 1440, 1) || ' minutes' AS live_status
FROM dba_scheduler_jobs
WHERE state = 'RUNNING'
AND job_name LIKE 'GATHER_STATS_%'
UNION ALL
SELECT 'No stats job currently running'
FROM dual
WHERE NOT EXISTS (
SELECT 1 FROM dba_scheduler_jobs
WHERE state = 'RUNNING' AND job_name LIKE 'GATHER_STATS_%'
);
------------------------------------------------------------------
-- 3. EXECUTIVE SUMMARY – 11g Compatible (No FETCH FIRST)
------------------------------------------------------------------
-- FIX: Use ROWNUM wrapper to support Oracle 11g
SELECT * FROM (
SELECT
job_name,
TO_CHAR(actual_start_date,'YYYY-MM-DD HH24:MI:SS') AS started,
status,
ROUND( (CAST(log_date AS DATE) - CAST(actual_start_date AS DATE)) * 1440, 1) AS minutes,
error#
FROM dba_scheduler_job_run_details
WHERE job_name LIKE 'GATHER_STATS_%'
ORDER BY actual_start_date DESC
) WHERE ROWNUM <= 20;
------------------------------------------------------------------
-- 4. MASTER VIEW – Current state + Last run details
------------------------------------------------------------------
SELECT
j.job_name,
j.enabled,
j.state,
j.next_run_date,
d.status AS last_run_status,
d.duration_minutes AS last_run_mins,
d.started
FROM dba_scheduler_jobs j
LEFT JOIN (
SELECT
job_name,
status,
TO_CHAR(actual_start_date,'YYYY-MM-DD HH24:MI:SS') AS started,
-- FIX: Cast to DATE ensures number math
ROUND( (CAST(log_date AS DATE) - CAST(actual_start_date AS DATE)) * 1440, 1) AS duration_minutes,
ROW_NUMBER() OVER (PARTITION BY job_name ORDER BY actual_start_date DESC) rn
FROM dba_scheduler_job_run_details
) d ON j.job_name = d.job_name AND d.rn = 1
WHERE j.job_name LIKE 'GATHER_STATS_%'
ORDER BY j.job_name;
------------------------------------------------------------------
-- 5. BONUS: Custom Log Summary (Fixed ORA-00932)
------------------------------------------------------------------
SELECT
log_time,
-- Calculate how many minutes ago this specific line happened
ROUND((CAST(SYSTIMESTAMP AS DATE) - CAST(log_time AS DATE)) * 1440, 1) || ' mins ago' as age,
message
FROM stats_job_log
WHERE job_name = (SELECT MAX(job_name) FROM stats_job_log) -- Get only the latest job
ORDER BY log_id DESC;
SELECT
job_name,
TO_CHAR(MIN(log_time), 'YYYY-MM-DD HH24:MI:SS') AS started_at,
-- CHECK STATUS: Look at the very last message written
CASE
WHEN MAX(message) KEEP (DENSE_RANK LAST ORDER BY log_id) LIKE 'COMPLETED%'
THEN 'FINISHED'
WHEN MAX(message) KEEP (DENSE_RANK LAST ORDER BY log_id) LIKE 'FAILED%'
THEN 'FAILED'
ELSE 'RUNNING'
END AS current_status,
-- DURATION: If Running, calc from NOW. If Finished, calc from End Time.
ROUND(
(CAST(
CASE
WHEN MAX(message) KEEP (DENSE_RANK LAST ORDER BY log_id) LIKE 'COMPLETED%'
THEN MAX(log_time)
ELSE SYSTIMESTAMP -- Use "Now" if still running
END
AS DATE) - CAST(MIN(log_time) AS DATE)) * 1440
, 2) AS duration_mins,
-- Progress Counters
COUNT(CASE WHEN message LIKE 'SUCCESS%' THEN 1 END) AS tables_done,
-- Show me the last thing it wrote (e.g., "Processing Table B...")
SUBSTR(MAX(message) KEEP (DENSE_RANK LAST ORDER BY log_id), 1, 50) AS last_activity
FROM stats_job_log
GROUP BY job_name
ORDER BY MIN(log_time) DESC;
------------------------------------------------------------------
-- PERFECT CUSTOM LOG SUMMARY – works whether job is running or not
------------------------------------------------------------------
SELECT
job_name,
-- 1. When did it start?
TO_CHAR(MIN(log_time), 'YYYY-MM-DD HH24:MI:SS') AS started,
-- 2. Did it finish? If yes → show finish time, if not → show "Still running"
MAX(CASE WHEN message LIKE 'COMPLETED%' THEN
TO_CHAR(log_time, 'YYYY-MM-DD HH24:MI:SS')
END) AS finished,
-- 3. Current status (the single most important column)
CASE
WHEN MAX(CASE WHEN message LIKE 'COMPLETED%' THEN 1 END) = 1
THEN 'Completed'
WHEN MAX(CASE WHEN message LIKE 'STARTED%' THEN 1 END) = 1
THEN 'In Progress'
ELSE 'Unknown'
END AS status,
-- 4. How long has it been running / how long did it take?
ROUND(
(CAST(SYSTIMESTAMP AS DATE) - CAST(MIN(log_time) AS DATE)) * 1440
, 1) AS elapsed_minutes,
-- 5. If finished → exact duration, if still running → null
CASE
WHEN MAX(CASE WHEN message LIKE 'COMPLETED%' THEN 1 END) = 1 THEN
ROUND(
(CAST(MAX(log_time) AS DATE) - CAST(MIN(log_time) AS DATE)) *1440
, 2)
END AS duration_if_finished_mins,
-- 6. Table counts
COUNT(CASE WHEN message LIKE 'SUCCESS %' THEN 1 END) AS tables_success,
COUNT(CASE WHEN message LIKE 'FAILED %' THEN 1 END) AS tables_failed,
COUNT(*) AS total_messages
FROM stats_job_log
GROUP BY job_name
ORDER BY MIN(log_time) DESC;
more"
SELECT
j.job_name,
j.enabled,
j.state AS job_state,
TO_CHAR(j.last_start_date, 'YYYY-MM-DD HH24:MI') AS last_started,
TO_CHAR(j.next_run_date, 'YYYY-MM-DD HH24:MI') AS next_scheduled,
d.status AS last_run_status,
TO_CHAR(d.last_run_time_raw, 'YYYY-MM-DD HH24:MI:SS') AS last_run_time,
d.duration_mins AS last_run_minutes,
d.error_code,
CASE
WHEN d.status = 'SUCCEEDED' THEN 'OK'
WHEN d.status = 'FAILED' THEN 'ERROR'
WHEN d.status IS NULL THEN 'Never run'
ELSE d.status
END AS health,
------------------------------------------------------------------
-- THE FIX: Explicitly CAST the result of NVL to DATE
------------------------------------------------------------------
ROUND(
SYSDATE - CAST( NVL(d.last_success_date, TO_DATE('1900-01-01','YYYY-MM-DD')) AS DATE )
, 1) AS days_since_last_success,
j.run_count,
j.failure_count
FROM dba_scheduler_jobs j
LEFT JOIN (
SELECT
job_name,
status,
error# AS error_code,
actual_start_date AS last_run_time_raw,
-- Duration Calculation (Safe)
ROUND(
(CAST(log_date AS DATE) - CAST(actual_start_date AS DATE)) * 1440
, 2) AS duration_mins,
-- Last Success Date (Raw)
MAX(CASE WHEN status = 'SUCCEEDED' THEN actual_start_date END)
OVER (PARTITION BY job_name) AS last_success_date,
ROW_NUMBER() OVER (PARTITION BY job_name
ORDER BY actual_start_date DESC) AS rn
FROM dba_scheduler_job_run_details
) d ON j.job_name = d.job_name AND d.rn = 1
WHERE j.job_name LIKE 'GATHER_STATS_%'
ORDER BY days_since_last_success DESC NULLS LAST, j.job_name;
=========
SET SERVEROUTPUT ON;
DECLARE
v_count NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('--- EMERGENCY STOP STARTED ---');
-- 1. KILL RUNNING JOBS
FOR r IN (
SELECT job_name
FROM dba_scheduler_running_jobs
WHERE job_name LIKE 'GATHER_STATS_%'
)
LOOP
BEGIN
DBMS_SCHEDULER.STOP_JOB(job_name => r.job_name, force => TRUE);
DBMS_OUTPUT.PUT_LINE('KILLED Running Job: ' || r.job_name);
v_count := v_count + 1;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Could not stop ' || r.job_name || ': ' || SQLERRM);
END;
END LOOP;
-- 2. CLEAN UP DEFINITIONS (Just in case)
FOR r IN (
SELECT job_name
FROM dba_scheduler_jobs
WHERE job_name LIKE 'GATHER_STATS_%'
)
LOOP
BEGIN
DBMS_SCHEDULER.DROP_JOB(job_name => r.job_name, force => TRUE);
DBMS_OUTPUT.PUT_LINE('DROPPED Job Definition: ' || r.job_name);
EXCEPTION WHEN OTHERS THEN
NULL; -- Ignore if it was already dropped by the STOP command
END;
END LOOP;
IF v_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('No active GATHER_STATS jobs found.');
ELSE
DBMS_OUTPUT.PUT_LINE('--- STOP COMPLETED ---');
END IF;
END;
/
