Great question! Oracle stores all stats collection history in the **data dictionary**. Let me show you exactly where.
---
## Where Oracle Stores This Info
```
┌─────────────────────────────────────────────────────────────┐
│ ORACLE DATA DICTIONARY │
├──────────────────────────┬──────────────────────────────────┤
│ WRI$_OPTSTAT_TAB_HISTORY │ Raw internal table stats history │
│ WRI$_OPTSTAT_IND_HISTORY │ Raw internal index stats history │
├──────────────────────────┼──────────────────────────────────┤
│ DBA_TAB_STATS_HISTORY │ Public view of table stats hist │
│ DBA_OPTSTAT_OPERATIONS │ Who ran it, when, what options │
├──────────────────────────┼──────────────────────────────────┤
│ DBA_SCHEDULER_JOB_LOG │ Job execution log (ran/failed) │
│ DBA_SCHEDULER_JOB_RUN_DETAILS │ Duration, errors, status │
└──────────────────────────┴──────────────────────────────────┘
```
---
## QUERY 1: What Operations Ran and When
```sql
-- Shows every stats gather operation, who triggered it, options used
SELECT
TO_CHAR(start_time, 'DY DD-MON-YYYY HH24:MI:SS') AS started_at,
TO_CHAR(end_time, 'DY DD-MON-YYYY HH24:MI:SS') AS ended_at,
ROUND((end_time - start_time) * 24 * 60, 2) AS duration_min,
operation,
target,
status,
notes
FROM dba_optstat_operations
WHERE start_time >= SYSDATE - 30 -- last 30 days, adjust freely
ORDER BY start_time DESC;
```
**Sample Output:**
```
STARTED_AT ENDED_AT DUR_MIN OPERATION TARGET STATUS
MON 12-MAY-2025 21:00:05 MON 12-MAY-2025 22:14:33 74.46 gather_schema_stats RDSADM COMPLETED
SAT 10-MAY-2025 21:00:01 SAT 10-MAY-2025 23:45:12 165.18 gather_database_stats SYS COMPLETED
```
---
## QUERY 2: Exactly Which Tables Were Picked
```sql
-- Every table that had stats gathered, with before/after snapshot
SELECT
h.owner,
h.table_name,
TO_CHAR(h.stats_update_time, 'DY DD-MON-YYYY HH24:MI:SS') AS stats_gathered_at,
t.num_rows,
t.blocks,
t.last_analyzed
FROM dba_tab_stats_history h
JOIN dba_tables t
ON t.owner = h.owner
AND t.table_name = h.table_name
WHERE h.stats_update_time >= SYSDATE - 7 -- last 7 days
ORDER BY h.stats_update_time DESC, h.owner, h.table_name;
```
---
## QUERY 3: Link Tables Back to a Specific Job Run
```sql
-- Match the job execution time window to the tables gathered in that window
SELECT
h.owner,
h.table_name,
TO_CHAR(h.stats_update_time, 'DY DD-MON-YYYY HH24:MI:SS') AS gathered_at,
op.operation,
op.target,
op.status
FROM dba_tab_stats_history h
JOIN dba_optstat_operations op
ON h.stats_update_time BETWEEN op.start_time AND op.end_time
WHERE op.start_time >= SYSDATE - 7
ORDER BY op.start_time DESC, h.owner, h.table_name;
```
---
## QUERY 4: Tables That Were Stale (Why Oracle Picked Them)
```sql
-- Oracle picks tables based on STALE_PERCENT (default 10% row change)
-- This shows which tables CURRENTLY qualify as stale
SELECT
owner,
table_name,
num_rows,
stale_stats,
last_analyzed,
ROUND((sysdate - last_analyzed), 1) AS days_since_analyzed
FROM dba_tab_statistics
WHERE owner = 'RDSADM' -- change to your schema
AND stale_stats = 'YES'
ORDER BY days_since_analyzed DESC NULLS FIRST;
```
---
## QUERY 5: Scheduler Job Execution Log
```sql
-- Shows each job run: start, duration, status
SELECT
log_date,
job_name,
owner,
operation,
status,
ROUND(actual_start_date - log_date, 4) AS delay_days,
run_duration,
error#,
additional_info
FROM dba_scheduler_job_run_details
WHERE owner = 'RDSADM'
AND job_name LIKE '%STAT%'
AND log_date >= SYSDATE - 30
ORDER BY log_date DESC;
```
---
## QUERY 6: Full Audit, Which Tables Per Job Run
```sql
-- The complete picture: job run + every table it touched
SELECT
d.log_date AS job_fired_at,
d.job_name,
d.status AS job_status,
d.run_duration,
h.owner AS schema_name,
h.table_name,
TO_CHAR(h.stats_update_time, 'DD-MON-YYYY HH24:MI:SS') AS table_stats_time,
t.num_rows,
ROUND(t.num_rows * t.avg_row_len / 1024 / 1024, 2) AS size_mb
FROM dba_scheduler_job_run_details d
JOIN dba_tab_stats_history h
ON h.stats_update_time BETWEEN d.actual_start_date
AND d.actual_start_date + d.run_duration
JOIN dba_tables t
ON t.owner = h.owner
AND t.table_name = h.table_name
WHERE d.owner = 'RDSADM'
AND d.job_name LIKE '%STAT%'
AND d.log_date >= SYSDATE - 7
ORDER BY d.log_date DESC, h.owner, h.table_name;
```
---
## QUERY 7: How Oracle Decides to Pick a Table (Staleness Logic)
```sql
-- Shows modification counts since last analyze
-- Oracle default threshold = 10% of num_rows changed = stale
SELECT
m.table_owner,
m.table_name,
m.inserts,
m.updates,
m.deletes,
m.truncated,
m.timestamp AS last_dml_time,
t.num_rows AS current_num_rows,
t.last_analyzed,
ROUND((m.inserts + m.updates + m.deletes)
/ NULLIF(t.num_rows, 0) * 100, 2) AS pct_changed,
CASE
WHEN (m.inserts + m.updates + m.deletes)
>= t.num_rows * 0.10
THEN 'WILL BE PICKED (STALE)'
ELSE 'CLEAN, SKIP'
END AS oracle_decision
FROM dba_tab_modifications m
JOIN dba_tables t
ON t.owner = m.table_owner
AND t.table_name = m.table_name
WHERE m.table_owner = 'RDSADM' -- change to your schema
ORDER BY pct_changed DESC NULLS LAST;
```
---
## Quick Reference Cheat Sheet
```
QUESTION VIEW TO QUERY
───────────────────────────────── ──────────────────────────────
When did the job run? DBA_SCHEDULER_JOB_RUN_DETAILS
What operation ran (gather/delete)? DBA_OPTSTAT_OPERATIONS
Which tables got new stats? DBA_TAB_STATS_HISTORY
Why was a table picked (stale)? DBA_TAB_MODIFICATIONS
What are the current stats? DBA_TAB_STATISTICS
What is table size / rows now? DBA_TABLES
Which tables are still stale NOW? DBA_TAB_STATISTICS (stale_stats='YES')
Raw internal history (DBA only)? WRI$_OPTSTAT_TAB_HISTORY
```
---
> **Key point:** Oracle picks a table automatically when DML changes exceed **10% of `num_rows`** since the last analyze. That threshold lives in `DBMS_STATS.GET_PREFS('STALE_PERCENT')` and can be changed per table if needed.
=============================================
Here is the updated query. I have added a new column to calculate the size in Gigabytes (GB) right next to the Megabytes (MB) column.
Since GB is a much larger unit, I set it to round to 3 decimal places (0.000) so that you can still see the values for smaller tables that haven't reached a full Gigabyte yet.
SQL
SELECT
t.table_name,
t.num_rows AS record_count,
ROUND(s.bytes / 1024 / 1024, 2) AS size_in_mb,
ROUND(s.bytes / 1024 / 1024 / 1024, 3) AS size_in_gb
FROM
all_tables t
LEFT JOIN
all_segments s ON t.owner = s.owner AND t.table_name = s.segment_name
WHERE
t.owner = 'schema_name'
AND s.segment_type = 'TABLE'
-- Table filters
AND (t.table_name LIKE 'NTR%' OR t.table_name LIKE '%ECU%')
AND NOT REGEXP_LIKE(t.table_name, 'VW|HIST|CHG|XREF|MD_|BKP|BKUP|TMP|DMS|MOD')
AND REGEXP_LIKE(t.table_name, '^[A-Z_]+$')
ORDER BY
size_in_gb DESC;
========================================
DECLARE
v_user VARCHAR2(30) := '';
v_group VARCHAR2(30) := 'CPU_LIMITED_USER_GROUP';
BEGIN
sys.dbms_resource_manager.clear_pending_area();
sys.dbms_resource_manager.create_pending_area();
-- 1. Grant the user permission to switch to this group
sys.dbms_resource_manager_privs.grant_switch_consumer_group (
grantee_name => v_user,
consumer_group => v_group,
grant_option => FALSE
);
-- 2. Set this group as the user's automatic default upon login
sys.dbms_resource_manager.set_initial_consumer_group (
user => v_user,
consumer_group => v_group
);
sys.dbms_resource_manager.submit_pending_area();
DBMS_OUTPUT.PUT_LINE('Success: ' || v_user || ' is now mapped to ' || v_group);
EXCEPTION
WHEN OTHERS THEN
sys.dbms_resource_manager.clear_pending_area();
DBMS_OUTPUT.PUT_LINE('Error applying Resource Manager mapping: ' || SQLERRM);
END;
/
========================
BEGIN
-- --------------------------------------------------------
-- LIGHT JOBS: 01, 02, 03, 07
-- Weeknights : MON and WED after business hours
-- Weekends : SAT and SUN twice (night run + afternoon run)
-- Staggered : 30 minutes apart within same batch
-- --------------------------------------------------------
-- JOB01: MON, WED nights 21:00 + SAT, SUN 21:00 and 12:00
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_01_JOB',
attribute => 'repeat_interval',
value => 'FREQ=WEEKLY;BYDAY=MON,WED,SAT,SUN;BYHOUR=21;BYMINUTE=00'
);
-- JOB02: MON, WED nights 21:30 + SAT, SUN 21:30 and 12:30
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_02_JOB',
attribute => 'repeat_interval',
value => 'FREQ=WEEKLY;BYDAY=MON,WED,SAT,SUN;BYHOUR=21;BYMINUTE=30'
);
-- JOB03: MON, WED nights 22:00 + SAT, SUN 22:00 and 13:00
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_03_JOB',
attribute => 'repeat_interval',
value => 'FREQ=WEEKLY;BYDAY=MON,WED,SAT,SUN;BYHOUR=22;BYMINUTE=00'
);
-- JOB07: MON, WED nights 22:30 + SAT, SUN 22:30 and 13:30
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_07_JOB',
attribute => 'repeat_interval',
value => 'FREQ=WEEKLY;BYDAY=MON,WED,SAT,SUN;BYHOUR=22;BYMINUTE=30'
);
-- --------------------------------------------------------
-- HEAVY JOBS: 04, 05, 06
-- Weeknights : TUE and THU late night (after midnight buffer)
-- Weekends : SAT and SUN twice (late night + afternoon)
-- 2.5 hour gaps between each heavy job
-- --------------------------------------------------------
-- JOB04: TUE, THU nights 23:30 + SAT, SUN 23:30 and 15:00
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
attribute => 'repeat_interval',
value => 'FREQ=WEEKLY;BYDAY=TUE,THU,SAT,SUN;BYHOUR=23;BYMINUTE=30'
);
-- JOB05: TUE, THU into early morning 02:00 + SAT, SUN 02:00 and 17:00
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_05_JOB',
attribute => 'repeat_interval',
value => 'FREQ=WEEKLY;BYDAY=TUE,THU,SAT,SUN;BYHOUR=02;BYMINUTE=00'
);
-- JOB06: TUE, THU into early morning 04:30 + SAT, SUN 04:30 and 19:00
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_06_JOB',
attribute => 'repeat_interval',
value => 'FREQ=WEEKLY;BYDAY=TUE,THU,SAT,SUN;BYHOUR=04;BYMINUTE=30'
);
DBMS_OUTPUT.PUT_LINE('All 7 PROD job schedules updated successfully.');
END;
/
Important Note on JOB05 and JOB06
⚠️ BYHOUR=02 on BYDAY=TUE means it fires at 02:00 on TUESDAY night
which is actually WEDNESDAY early morning 02:00
Oracle scheduler interprets BYDAY as the calendar day
So TUE at 02:00 = Tuesday 02:00 AM (early hours of Tuesday)
If you want it to fire after TUE 23:30 (JOB04),
it should be WED at 02:00 and FRI at 02:00
Corrected Heavy Job Schedule
BEGIN
-- JOB04 fires TUE and THU at 23:30
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
attribute => 'repeat_interval',
value => 'FREQ=WEEKLY;BYDAY=TUE,THU,SAT,SUN;BYHOUR=23;BYMINUTE=30'
);
-- JOB05 fires WED and FRI at 02:00
-- (2.5 hours after JOB04 which started TUE/THU 23:30)
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_05_JOB',
attribute => 'repeat_interval',
value => 'FREQ=WEEKLY;BYDAY=WED,FRI,SAT,SUN;BYHOUR=02;BYMINUTE=00'
);
-- JOB06 fires WED and FRI at 04:30
-- (2.5 hours after JOB05 which started WED/FRI 02:00)
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_06_JOB',
attribute => 'repeat_interval',
value => 'FREQ=WEEKLY;BYDAY=WED,FRI,SAT,SUN;BYHOUR=04;BYMINUTE=30'
);
DBMS_OUTPUT.PUT_LINE('Heavy job schedules corrected and updated.');
END;
/
Verify Next Run Dates
SELECT
job_name,
enabled,
repeat_interval,
TO_CHAR(next_run_date, 'DY DD-MON-YYYY HH24:MI') AS next_run
FROM dba_scheduler_jobs
WHERE owner = 'RDSADM'
AND job_name IN (
'GATHER_SCHEMA_STATS_01_JOB',
'GATHER_SCHEMA_STATS_02_JOB',
'GATHER_SCHEMA_STATS_03_JOB',
'GATHER_SCHEMA_STATS_04_JOB',
'GATHER_SCHEMA_STATS_05_JOB',
'GATHER_SCHEMA_STATS_06_JOB',
'GATHER_SCHEMA_STATS_07_JOB'
)
ORDER BY next_run_date;
=======================================
Sure! Here is a clean, simple, no-fluff approach using just SQL and minimal PL/SQL blocks.
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_01_JOB',
attribute => 'repeat_interval',
value => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SUN;BYHOUR=00;BYMINUTE=00'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_02_JOB',
attribute => 'repeat_interval',
value => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SUN;BYHOUR=03;BYMINUTE=00'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_03_JOB',
attribute => 'repeat_interval',
value => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SUN;BYHOUR=06;BYMINUTE=00'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
attribute => 'repeat_interval',
value => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SUN;BYHOUR=09;BYMINUTE=00'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_05_JOB',
attribute => 'repeat_interval',
value => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SUN;BYHOUR=12;BYMINUTE=00'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_06_JOB',
attribute => 'repeat_interval',
value => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SUN;BYHOUR=15;BYMINUTE=00'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_07_JOB',
attribute => 'repeat_interval',
value => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SUN;BYHOUR=18;BYMINUTE=00'
);
END;
/
===============
Yes, you are right. If 3 schema-stats jobs run at 03,09,15,21, the DB can get congested and create unnecessary CPU / I/O pressure.
For stats jobs, do not let 01, 04, and 07 start together. Stagger them.
1. First verify current schedules
SET LINES 300
SET PAGES 200
COLUMN owner FORMAT A15
COLUMN job_name FORMAT A35
COLUMN enabled FORMAT A10
COLUMN state FORMAT A15
COLUMN repeat_interval FORMAT A120
COLUMN next_run_date FORMAT A45
SELECT owner,
job_name,
enabled,
state,
repeat_interval,
next_run_date
FROM dba_scheduler_jobs
WHERE owner = 'RDSADM'
AND job_name IN (
'GATHER_SCHEMA_STATS_01_JOB',
'GATHER_SCHEMA_STATS_02_JOB',
'GATHER_SCHEMA_STATS_03_JOB',
'GATHER_SCHEMA_STATS_04_JOB',
'GATHER_SCHEMA_STATS_05_JOB',
'GATHER_SCHEMA_STATS_06_JOB',
'GATHER_SCHEMA_STATS_07_JOB'
)
ORDER BY job_name;
2. Recommended staggered schedule
Use this pattern so only one stats job starts per slot:
01 job -> 00,06,12,18
02 job -> 01,07,13,19
03 job -> 02,08,14,20
04 job -> 03,09,15,21
05 job -> 04,10,16,22
06 job -> 05,11,17,23
07 job -> 00:30,06:30,12:30,18:30
This removes the duplicate 03,09,15,21 collision.
3. Apply staggered schedules
BEGIN
DBMS_SCHEDULER.DISABLE('RDSADM.GATHER_SCHEMA_STATS_01_JOB', TRUE);
DBMS_SCHEDULER.DISABLE('RDSADM.GATHER_SCHEMA_STATS_02_JOB', TRUE);
DBMS_SCHEDULER.DISABLE('RDSADM.GATHER_SCHEMA_STATS_03_JOB', TRUE);
DBMS_SCHEDULER.DISABLE('RDSADM.GATHER_SCHEMA_STATS_04_JOB', TRUE);
DBMS_SCHEDULER.DISABLE('RDSADM.GATHER_SCHEMA_STATS_05_JOB', TRUE);
DBMS_SCHEDULER.DISABLE('RDSADM.GATHER_SCHEMA_STATS_06_JOB', TRUE);
DBMS_SCHEDULER.DISABLE('RDSADM.GATHER_SCHEMA_STATS_07_JOB', TRUE);
END;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_01_JOB',
attribute => 'repeat_interval',
value => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SUN;BYHOUR=00,06,12,18;BYMINUTE=00'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_02_JOB',
attribute => 'repeat_interval',
value => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SUN;BYHOUR=01,07,13,19;BYMINUTE=00'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_03_JOB',
attribute => 'repeat_interval',
value => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SUN;BYHOUR=02,08,14,20;BYMINUTE=00'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
attribute => 'repeat_interval',
value => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SUN;BYHOUR=03,09,15,21;BYMINUTE=00'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_05_JOB',
attribute => 'repeat_interval',
value => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SUN;BYHOUR=04,10,16,22;BYMINUTE=00'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_06_JOB',
attribute => 'repeat_interval',
value => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SUN;BYHOUR=05,11,17,23;BYMINUTE=00'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_07_JOB',
attribute => 'repeat_interval',
value => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SUN;BYHOUR=00,06,12,18;BYMINUTE=30'
);
END;
/
4. Enable them again
BEGIN
DBMS_SCHEDULER.ENABLE('RDSADM.GATHER_SCHEMA_STATS_01_JOB');
DBMS_SCHEDULER.ENABLE('RDSADM.GATHER_SCHEMA_STATS_02_JOB');
DBMS_SCHEDULER.ENABLE('RDSADM.GATHER_SCHEMA_STATS_03_JOB');
DBMS_SCHEDULER.ENABLE('RDSADM.GATHER_SCHEMA_STATS_04_JOB');
DBMS_SCHEDULER.ENABLE('RDSADM.GATHER_SCHEMA_STATS_05_JOB');
DBMS_SCHEDULER.ENABLE('RDSADM.GATHER_SCHEMA_STATS_06_JOB');
DBMS_SCHEDULER.ENABLE('RDSADM.GATHER_SCHEMA_STATS_07_JOB');
END;
/
5. Final verification
SET LINES 300
SET PAGES 200
COLUMN owner FORMAT A15
COLUMN job_name FORMAT A35
COLUMN enabled FORMAT A10
COLUMN state FORMAT A15
COLUMN repeat_interval FORMAT A120
COLUMN next_run_date FORMAT A45
SELECT owner,
job_name,
enabled,
state,
repeat_interval,
next_run_date
FROM dba_scheduler_jobs
WHERE owner = 'RDSADM'
AND job_name IN (
'GATHER_SCHEMA_STATS_01_JOB',
'GATHER_SCHEMA_STATS_02_JOB',
'GATHER_SCHEMA_STATS_03_JOB',
'GATHER_SCHEMA_STATS_04_JOB',
'GATHER_SCHEMA_STATS_05_JOB',
'GATHER_SCHEMA_STATS_06_JOB',
'GATHER_SCHEMA_STATS_07_JOB'
)
ORDER BY next_run_date;
Important note
This is better than 3 jobs starting together, but if each stats job runs longer than 30–60 minutes, they can still overlap. The safest production design is one coordinator job that runs 01 → 02 → 03 → 04 → 05 → 06 → 07 serially during a maintenance window. For now, this staggered schedule is a good immediate fix to reduce DB pressure.
===================================================
Clean toggle for all 7 jobs in a loop:
```sql
SET SERVEROUTPUT ON
DECLARE
v_status_before VARCHAR2(10);
v_status_after VARCHAR2(10);
v_owner VARCHAR2(128);
v_enabled_count NUMBER := 0;
v_disabled_count NUMBER := 0;
v_failed_count NUMBER := 0;
TYPE t_job_list IS TABLE OF VARCHAR2(128);
v_jobs t_job_list := t_job_list(
'GATHER_SCHEMA_STATS_01_JOB',
'GATHER_SCHEMA_STATS_02_JOB',
'GATHER_SCHEMA_STATS_03_JOB',
'GATHER_SCHEMA_STATS_04_JOB',
'GATHER_SCHEMA_STATS_05_JOB',
'GATHER_SCHEMA_STATS_06_JOB',
'GATHER_SCHEMA_STATS_07_JOB'
);
BEGIN
DBMS_OUTPUT.PUT_LINE('================================================');
DBMS_OUTPUT.PUT_LINE(' Stats Job Toggle - ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('================================================');
FOR i IN 1 .. v_jobs.COUNT LOOP
BEGIN
-- Get current status and owner from dba_scheduler_jobs
SELECT enabled,
owner
INTO v_status_before,
v_owner
FROM dba_scheduler_jobs
WHERE job_name = v_jobs(i)
AND ROWNUM = 1;
-- Toggle based on current status
IF v_status_before = 'TRUE' THEN
DBMS_SCHEDULER.DISABLE(
name => v_owner || '.' || v_jobs(i),
force => TRUE
);
ELSIF v_status_before = 'FALSE' THEN
DBMS_SCHEDULER.ENABLE(
name => v_owner || '.' || v_jobs(i)
);
ELSE
DBMS_OUTPUT.PUT_LINE(' [SKIP] ' || v_jobs(i)
|| ' - Unexpected status: ' || v_status_before);
v_failed_count := v_failed_count + 1;
CONTINUE;
END IF;
-- Confirm new status after toggle
SELECT enabled
INTO v_status_after
FROM dba_scheduler_jobs
WHERE job_name = v_jobs(i)
AND ROWNUM = 1;
-- Count results
IF v_status_after = 'TRUE' THEN
v_enabled_count := v_enabled_count + 1;
ELSE
v_disabled_count := v_disabled_count + 1;
END IF;
-- Safety check
IF v_status_before = v_status_after THEN
DBMS_OUTPUT.PUT_LINE(' [WARNING] ' || v_jobs(i)
|| ' - Status did not change, verify manually.');
v_failed_count := v_failed_count + 1;
ELSE
DBMS_OUTPUT.PUT_LINE(' [OK] '
|| RPAD(v_jobs(i), 35)
|| ' | '
|| CASE v_status_before
WHEN 'TRUE' THEN 'ENABLED '
WHEN 'FALSE' THEN 'DISABLED'
END
|| ' --> '
|| CASE v_status_after
WHEN 'TRUE' THEN 'ENABLED'
WHEN 'FALSE' THEN 'DISABLED'
END);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(' [NOT FOUND] ' || v_jobs(i)
|| ' - Not found in dba_scheduler_jobs.');
v_failed_count := v_failed_count + 1;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' [ERROR] ' || v_jobs(i)
|| ' - ' || SQLERRM);
v_failed_count := v_failed_count + 1;
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('================================================');
DBMS_OUTPUT.PUT_LINE(' Toggled to ENABLED : ' || v_enabled_count);
DBMS_OUTPUT.PUT_LINE(' Toggled to DISABLED : ' || v_disabled_count);
DBMS_OUTPUT.PUT_LINE(' Failed / Not Found : ' || v_failed_count);
DBMS_OUTPUT.PUT_LINE('================================================');
END;
/
```
---
## Sample Output
### When all jobs are currently ENABLED (before maintenance)
```
================================================
Stats Job Toggle - 2026-05-11 08:30:00
================================================
[OK] GATHER_SCHEMA_STATS_01_JOB | ENABLED --> DISABLED
[OK] GATHER_SCHEMA_STATS_02_JOB | ENABLED --> DISABLED
[OK] GATHER_SCHEMA_STATS_03_JOB | ENABLED --> DISABLED
[OK] GATHER_SCHEMA_STATS_04_JOB | ENABLED --> DISABLED
[OK] GATHER_SCHEMA_STATS_05_JOB | ENABLED --> DISABLED
[OK] GATHER_SCHEMA_STATS_06_JOB | ENABLED --> DISABLED
[OK] GATHER_SCHEMA_STATS_07_JOB | ENABLED --> DISABLED
================================================
Toggled to ENABLED : 0
Toggled to DISABLED : 7
Failed / Not Found : 0
================================================
```
### When all jobs are currently DISABLED (after maintenance)
```
================================================
Stats Job Toggle - 2026-05-11 09:00:00
================================================
[OK] GATHER_SCHEMA_STATS_01_JOB | DISABLED --> ENABLED
[OK] GATHER_SCHEMA_STATS_02_JOB | DISABLED --> ENABLED
[OK] GATHER_SCHEMA_STATS_03_JOB | DISABLED --> ENABLED
[OK] GATHER_SCHEMA_STATS_04_JOB | DISABLED --> ENABLED
[OK] GATHER_SCHEMA_STATS_05_JOB | DISABLED --> ENABLED
[OK] GATHER_SCHEMA_STATS_06_JOB | DISABLED --> ENABLED
[OK] GATHER_SCHEMA_STATS_07_JOB | DISABLED --> ENABLED
================================================
Toggled to ENABLED : 7
Toggled to DISABLED : 0
Failed / Not Found : 0
================================================
```
---
## What It Handles
| Scenario | Action |
|---|---|
| Job is ENABLED | Disables it with `force => TRUE` |
| Job is DISABLED | Enables it |
| Job not found in DB | Logs `[NOT FOUND]`, continues to next |
| Toggle silently fails | Logs `[WARNING]`, flags in counter |
| Any unexpected error | Logs `[ERROR]`, continues to next |
| Owner auto-detected | No hardcoding needed |
=====================================
Yes. For GATHER_SCHEMA_STATS_04_JOB, make it follow the same scheduled pattern as the others.
Based on your screenshot, the natural missing schedule for 04 should be:
03:00, 09:00, 15:00, 21:00
Monday–Friday and Sunday
Because existing jobs are staggered like:
02 -> 01,07,13,19
03 -> 02,08,14,20
04 -> 03,09,15,21 <-- missing
05 -> 04,10,16,22
06 -> 05,11,17,23:30
07 -> 03,09,15,21:30
1. First confirm the procedure exists
SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE owner = 'RDSADM'
AND object_name = 'GATHER_SCHEMA_STATS_04';
If missing, create it:
CREATE OR REPLACE PROCEDURE RDSADM.GATHER_SCHEMA_STATS_04
AS
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'SCHEMA_NAME',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => DBMS_STATS.AUTO_DEGREE,
granularity => 'AUTO',
cascade => DBMS_STATS.AUTO_CASCADE,
options => 'GATHER AUTO',
no_invalidate => DBMS_STATS.AUTO_INVALIDATE
);
END;
/
Verify:
SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE owner = 'RDSADM'
AND object_name = 'GATHER_SCHEMA_STATS_04';
2. Create GATHER_SCHEMA_STATS_04_JOB with schedule
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'RDSADM.GATHER_SCHEMA_STATS_04',
start_date => TO_TIMESTAMP_TZ(
'2023/08/15 00:00:00.000000 +00:00',
'yyyy/mm/dd hh24:mi:ss.ff tzh:tzm'
),
repeat_interval => 'freq=daily;ByDay=Mon, Tue, Wed, Thu, Fri, Sun;byhour=03,09,15,21;BYMINUTE=00',
end_date => NULL,
job_class => 'DEFAULT_JOB_CLASS',
enabled => FALSE,
auto_drop => FALSE,
comments => 'Scheduled schema stats job for OWNER'
);
END;
/
3. Set same attributes as the others
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
attribute => 'RESTARTABLE',
value => FALSE
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
attribute => 'LOGGING_LEVEL',
value => DBMS_SCHEDULER.LOGGING_OFF
);
DBMS_SCHEDULER.SET_ATTRIBUTE_NULL(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
attribute => 'MAX_FAILURES'
);
DBMS_SCHEDULER.SET_ATTRIBUTE_NULL(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
attribute => 'MAX_RUNS'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
attribute => 'STOP_ON_WINDOW_CLOSE',
value => FALSE
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
attribute => 'JOB_PRIORITY',
value => 3
);
DBMS_SCHEDULER.SET_ATTRIBUTE_NULL(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
attribute => 'SCHEDULE_LIMIT'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
attribute => 'AUTO_DROP',
value => FALSE
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
attribute => 'RESTART_ON_RECOVERY',
value => FALSE
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
attribute => 'RESTART_ON_FAILURE',
value => FALSE
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
attribute => 'STORE_OUTPUT',
value => TRUE
);
END;
/
4. Enable it so it follows the schedule
BEGIN
DBMS_SCHEDULER.ENABLE(
name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB'
);
END;
/
5. Verify 01–07 schedule
SET LINES 300
SET PAGES 200
COLUMN owner FORMAT A15
COLUMN job_name FORMAT A35
COLUMN enabled FORMAT A10
COLUMN state FORMAT A15
COLUMN repeat_interval FORMAT A100
COLUMN next_run_date FORMAT A45
SELECT owner,
job_name,
enabled,
state,
repeat_interval,
next_run_date
FROM dba_scheduler_jobs
WHERE owner = 'RDSADM'
AND job_name IN (
'GATHER_SCHEMA_STATS_01_JOB',
'GATHER_SCHEMA_STATS_02_JOB',
'GATHER_SCHEMA_STATS_03_JOB',
'GATHER_SCHEMA_STATS_04_JOB',
'GATHER_SCHEMA_STATS_05_JOB',
'GATHER_SCHEMA_STATS_06_JOB',
'GATHER_SCHEMA_STATS_07_JOB'
)
ORDER BY job_name;
6. To manually run only 04 during maintenance
BEGIN
DBMS_SCHEDULER.RUN_JOB(
job_name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
use_current_session => FALSE
);
END;
/
Monitor:
SELECT owner,
job_name,
session_id,
running_instance,
elapsed_time,
cpu_used
FROM dba_scheduler_running_jobs
WHERE owner = 'RDSADM'
AND job_name = 'GATHER_SCHEMA_STATS_04_JOB';
7. Fix broken GATHER_SCHEMA_STATS_01_JOB
Run this to reset the broken state:
ALTER PROCEDURE RDSADM.GATHER_SCHEMA_STATS_01 COMPILE;
Then:
BEGIN
DBMS_SCHEDULER.DISABLE(
name => 'RDSADM.GATHER_SCHEMA_STATS_01_JOB',
force => TRUE
);
END;
/
BEGIN
DBMS_SCHEDULER.ENABLE(
name => 'RDSADM.GATHER_SCHEMA_STATS_01_JOB'
);
END;
/
Verify:
SELECT owner,
job_name,
enabled,
state,
next_run_date
FROM dba_scheduler_jobs
WHERE owner = 'RDSADM'
AND job_name = 'GATHER_SCHEMA_STATS_01_JOB';
Expected:
ENABLED = TRUE
STATE = SCHEDULED
If it goes back to BROKEN, check the failure reason:
SELECT owner,
job_name,
status,
actual_start_date,
run_duration,
error#,
additional_info
FROM dba_scheduler_job_run_details
WHERE owner = 'RDSADM'
AND job_name = 'GATHER_SCHEMA_STATS_01_JOB'
ORDER BY actual_start_date DESC
FETCH FIRST 5 ROWS ONLY;
For 04, the key change from your earlier script is this:
repeat_interval => 'freq=daily;ByDay=Mon, Tue, Wed, Thu, Fri, Sun;byhour=03,09,15,21;BYMINUTE=00',
enabled => TRUE
That makes it follow the same scheduled pattern as the rest.
====================================================
SET SERVEROUTPUT ON
DECLARE
v_status_before VARCHAR2(30);
v_status_after VARCHAR2(30);
BEGIN
-- Check current status
SELECT status
INTO v_status_before
FROM dba_autotask_client
WHERE client_name = 'auto optimizer stats collection';
DBMS_OUTPUT.PUT_LINE('Current status : ' || v_status_before);
-- Toggle based on current status
IF v_status_before = 'ENABLED' THEN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL
);
ELSIF v_status_before = 'DISABLED' THEN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL
);
ELSE
DBMS_OUTPUT.PUT_LINE('Unexpected status: ' || v_status_before
|| ' - No action taken.');
RETURN;
END IF;
-- Confirm new status after toggle
SELECT status
INTO v_status_after
FROM dba_autotask_client
WHERE client_name = 'auto optimizer stats collection';
DBMS_OUTPUT.PUT_LINE('New status : ' || v_status_after);
DBMS_OUTPUT.PUT_LINE('Result : ' || v_status_before
|| ' --> ' || v_status_after);
-- Safety check: confirm toggle actually worked
IF v_status_before = v_status_after THEN
DBMS_OUTPUT.PUT_LINE('WARNING: Status did not change, verify manually.');
ELSE
DBMS_OUTPUT.PUT_LINE('Toggle successful.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ERROR: Client not found in dba_autotask_client.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);
END;
/
Yes — if only these jobs should run:
GATHER_SCHEMA_STATS_01_JOB
GATHER_SCHEMA_STATS_02_JOB
GATHER_SCHEMA_STATS_03_JOB
GATHER_SCHEMA_STATS_04_JOB
GATHER_SCHEMA_STATS_05_JOB
GATHER_SCHEMA_STATS_06_JOB
GATHER_SCHEMA_STATS_07_JOB
use the scripts below.
1. Pre-check: show only 01–07 stats jobs
SET LINES 250
SET PAGES 200
COLUMN owner FORMAT A15
COLUMN job_name FORMAT A35
COLUMN enabled FORMAT A10
COLUMN state FORMAT A15
COLUMN job_action FORMAT A60
SELECT owner,
job_name,
enabled,
state,
job_type,
job_action
FROM dba_scheduler_jobs
WHERE owner = 'RDSADM'
AND job_name IN (
'GATHER_SCHEMA_STATS_01_JOB',
'GATHER_SCHEMA_STATS_02_JOB',
'GATHER_SCHEMA_STATS_03_JOB',
'GATHER_SCHEMA_STATS_04_JOB',
'GATHER_SCHEMA_STATS_05_JOB',
'GATHER_SCHEMA_STATS_06_JOB',
'GATHER_SCHEMA_STATS_07_JOB'
)
ORDER BY job_name;
2. If GATHER_SCHEMA_STATS_04_JOB is missing, create it
First create/confirm the procedure:
CREATE OR REPLACE PROCEDURE RDSADM.GATHER_SCHEMA_STATS_04
AS
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'SCHEMA',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => DBMS_STATS.AUTO_DEGREE,
granularity => 'AUTO',
cascade => DBMS_STATS.AUTO_CASCADE,
options => 'GATHER AUTO',
no_invalidate => DBMS_STATS.AUTO_INVALIDATE
);
END;
/
Then create the job:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'RDSADM.GATHER_SCHEMA_STATS_04',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'Manual maintenance-window schema stats job for SCHEMA_NAME'
);
END;
/
Verify:
SELECT owner,
job_name,
enabled,
state,
job_action
FROM dba_scheduler_jobs
WHERE owner = 'RDSADM'
AND job_name = 'GATHER_SCHEMA_STATS_04_JOB';
3. Fix the broken GATHER_SCHEMA_STATS_01_JOB
First check last failure:
SELECT owner,
job_name,
status,
actual_start_date,
run_duration,
error#,
additional_info
FROM dba_scheduler_job_run_details
WHERE owner = 'RDSADM'
AND job_name = 'GATHER_SCHEMA_STATS_01_JOB'
ORDER BY actual_start_date DESC
FETCH FIRST 5 ROWS ONLY;
Compile the procedure:
ALTER PROCEDURE RDSADM.GATHER_SCHEMA_STATS_01 COMPILE;
Check procedure status:
SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE owner = 'RDSADM'
AND object_name = 'GATHER_SCHEMA_STATS_01';
Reset the broken scheduler job:
BEGIN
DBMS_SCHEDULER.DISABLE(
name => 'RDSADM.GATHER_SCHEMA_STATS_01_JOB',
force => TRUE
);
END;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE_NULL(
name => 'RDSADM.GATHER_SCHEMA_STATS_01_JOB',
attribute => 'MAX_FAILURES'
);
DBMS_SCHEDULER.SET_ATTRIBUTE_NULL(
name => 'RDSADM.GATHER_SCHEMA_STATS_01_JOB',
attribute => 'MAX_RUNS'
);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'RDSADM.GATHER_SCHEMA_STATS_01_JOB',
attribute => 'RESTART_ON_FAILURE',
value => FALSE
);
END;
/
Enable and disable once to clear the broken state, then keep it disabled until maintenance:
BEGIN
DBMS_SCHEDULER.ENABLE('RDSADM.GATHER_SCHEMA_STATS_01_JOB');
DBMS_SCHEDULER.DISABLE(
name => 'RDSADM.GATHER_SCHEMA_STATS_01_JOB',
force => TRUE
);
END;
/
Verify:
SELECT owner,
job_name,
enabled,
state
FROM dba_scheduler_jobs
WHERE owner = 'RDSADM'
AND job_name = 'GATHER_SCHEMA_STATS_01_JOB';
Expected after reset:
ENABLED = FALSE
STATE = DISABLED
4. During maintenance window: enable only 01–07 jobs
BEGIN
FOR r IN (
SELECT owner,
job_name
FROM dba_scheduler_jobs
WHERE owner = 'RDSADM'
AND job_name IN (
'GATHER_SCHEMA_STATS_01_JOB',
'GATHER_SCHEMA_STATS_02_JOB',
'GATHER_SCHEMA_STATS_03_JOB',
'GATHER_SCHEMA_STATS_04_JOB',
'GATHER_SCHEMA_STATS_05_JOB',
'GATHER_SCHEMA_STATS_06_JOB',
'GATHER_SCHEMA_STATS_07_JOB'
)
)
LOOP
DBMS_SCHEDULER.ENABLE(r.owner || '.' || r.job_name);
END LOOP;
END;
/
Verify:
SELECT owner,
job_name,
enabled,
state
FROM dba_scheduler_jobs
WHERE owner = 'RDSADM'
AND job_name IN (
'GATHER_SCHEMA_STATS_01_JOB',
'GATHER_SCHEMA_STATS_02_JOB',
'GATHER_SCHEMA_STATS_03_JOB',
'GATHER_SCHEMA_STATS_04_JOB',
'GATHER_SCHEMA_STATS_05_JOB',
'GATHER_SCHEMA_STATS_06_JOB',
'GATHER_SCHEMA_STATS_07_JOB'
)
ORDER BY job_name;
5. Start only these 01–07 jobs in background
This will start all 7 in parallel.
BEGIN
FOR r IN (
SELECT owner,
job_name
FROM dba_scheduler_jobs
WHERE owner = 'RDSADM'
AND job_name IN (
'GATHER_SCHEMA_STATS_01_JOB',
'GATHER_SCHEMA_STATS_02_JOB',
'GATHER_SCHEMA_STATS_03_JOB',
'GATHER_SCHEMA_STATS_04_JOB',
'GATHER_SCHEMA_STATS_05_JOB',
'GATHER_SCHEMA_STATS_06_JOB',
'GATHER_SCHEMA_STATS_07_JOB'
)
ORDER BY job_name
)
LOOP
DBMS_SCHEDULER.RUN_JOB(
job_name => r.owner || '.' || r.job_name,
use_current_session => FALSE
);
END LOOP;
END;
/
If you want safer execution, run them one by one instead of all 7 together:
BEGIN
DBMS_SCHEDULER.RUN_JOB('RDSADM.GATHER_SCHEMA_STATS_01_JOB', FALSE);
END;
/
BEGIN
DBMS_SCHEDULER.RUN_JOB('RDSADM.GATHER_SCHEMA_STATS_02_JOB', FALSE);
END;
/
BEGIN
DBMS_SCHEDULER.RUN_JOB('RDSADM.GATHER_SCHEMA_STATS_03_JOB', FALSE);
END;
/
BEGIN
DBMS_SCHEDULER.RUN_JOB('RDSADM.GATHER_SCHEMA_STATS_04_JOB', FALSE);
END;
/
BEGIN
DBMS_SCHEDULER.RUN_JOB('RDSADM.GATHER_SCHEMA_STATS_05_JOB', FALSE);
END;
/
BEGIN
DBMS_SCHEDULER.RUN_JOB('RDSADM.GATHER_SCHEMA_STATS_06_JOB', FALSE);
END;
/
BEGIN
DBMS_SCHEDULER.RUN_JOB('RDSADM.GATHER_SCHEMA_STATS_07_JOB', FALSE);
END;
/
Given your prior CPU/resource-manager issue, I would start with one or two jobs at a time, not all 7 together.
6. Monitor running stats jobs
SELECT owner,
job_name,
session_id,
running_instance,
elapsed_time,
cpu_used
FROM dba_scheduler_running_jobs
WHERE owner = 'RDSADM'
AND job_name LIKE 'GATHER_SCHEMA_STATS%JOB'
ORDER BY job_name;
Check what stats are running:
SELECT operation,
target,
status,
start_time,
end_time
FROM dba_optstat_operations
WHERE start_time >= SYSTIMESTAMP - INTERVAL '6' HOUR
ORDER BY start_time DESC;
7. After maintenance: disable only 01–07 jobs again
Run this after all jobs complete.
BEGIN
FOR r IN (
SELECT owner,
job_name
FROM dba_scheduler_jobs
WHERE owner = 'RDSADM'
AND job_name IN (
'GATHER_SCHEMA_STATS_01_JOB',
'GATHER_SCHEMA_STATS_02_JOB',
'GATHER_SCHEMA_STATS_03_JOB',
'GATHER_SCHEMA_STATS_04_JOB',
'GATHER_SCHEMA_STATS_05_JOB',
'GATHER_SCHEMA_STATS_06_JOB',
'GATHER_SCHEMA_STATS_07_JOB'
)
)
LOOP
DBMS_SCHEDULER.DISABLE(
name => r.owner || '.' || r.job_name,
force => TRUE
);
END LOOP;
END;
/
Verify they are disabled:
SELECT owner,
job_name,
enabled,
state
FROM dba_scheduler_jobs
WHERE owner = 'RDSADM'
AND job_name IN (
'GATHER_SCHEMA_STATS_01_JOB',
'GATHER_SCHEMA_STATS_02_JOB',
'GATHER_SCHEMA_STATS_03_JOB',
'GATHER_SCHEMA_STATS_04_JOB',
'GATHER_SCHEMA_STATS_05_JOB',
'GATHER_SCHEMA_STATS_06_JOB',
'GATHER_SCHEMA_STATS_07_JOB'
)
ORDER BY job_name;
How do 3K+ tables inside 04 schema run?
You do not create one job per table.
GATHER_SCHEMA_STATS_04_JOB calls this:
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'DB',
options => 'GATHER AUTO'
);
Oracle internally loops through the schema and gathers stats only for objects that need it: missing stats, stale stats, table/partition/subpartition stats, and index stats depending on the procedure parameters.
To check how many 04 tables need stats before running:
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SELECT stale_stats,
COUNT(*) AS table_count
FROM dba_tab_statistics
WHERE owner = 'DB'
AND object_type = 'TABLE'
GROUP BY stale_stats
ORDER BY stale_stats;
To list them:
SELECT table_name,
stale_stats,
last_analyzed
FROM dba_tab_statistics
WHERE owner = 'DB'
AND object_type = 'TABLE'
AND (stale_stats = 'YES' OR last_analyzed IS NULL)
ORDER BY last_analyzed NULLS FIRST;
My recommendation: create/fix the jobs now, keep them disabled, enable/run them only during the maintenance window, then disable them again after completion.
Yes — GATHER_SCHEMA_STATS_04_JOB is missing. Create it as a controlled maintenance-window job, not as something that starts immediately.
Assuming schema is:
1. Check whether the procedure exists
SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE owner = 'RDSADM'
AND object_name = 'GATHER_SCHEMA_STATS_04';
If it returns no rows, create the procedure below.
2. Create procedure for 04 schema stats
This will gather only missing/stale stats, not blindly all 3K+ tables every time.
CREATE OR REPLACE PROCEDURE RDSADM.GATHER_SCHEMA_STATS_04
AS
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => 'GATHER_SCHEMA_STATS_04_JOB',
action_name => 'DBMS_STATS.GATHER_SCHEMA_STATS'
);
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'DB',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => DBMS_STATS.AUTO_DEGREE,
granularity => 'AUTO',
cascade => DBMS_STATS.AUTO_CASCADE,
options => 'GATHER AUTO',
no_invalidate => DBMS_STATS.AUTO_INVALIDATE
);
END;
/
Verify:
SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE owner = 'RDSADM'
AND object_name = 'GATHER_SCHEMA_STATS_04';
3. Create the scheduler job, disabled initially
This creates the job but does not run it immediately.
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'RDSADM.GATHER_SCHEMA_STATS_04',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'Manual maintenance-window job to gather SCHEMA schema stats'
);
END;
/
Verify job exists:
SELECT owner,
job_name,
enabled,
state,
repeat_interval
FROM dba_scheduler_jobs
WHERE owner = 'RDSADM'
AND job_name = 'GATHER_SCHEMA_STATS_04_JOB';
4. Run it manually during maintenance window
BEGIN
DBMS_SCHEDULER.RUN_JOB(
job_name => 'RDSADM.GATHER_SCHEMA_STATS_04_JOB',
use_current_session => FALSE
);
END;
/
Because use_current_session => FALSE, it runs in the background.
5. Monitor while running
SELECT owner,
job_name,
session_id,
running_instance,
elapsed_time,
cpu_used
FROM dba_scheduler_running_jobs
WHERE owner = 'RDSADM'
AND job_name = 'GATHER_SCHEMA_STATS_04_JOB';
Check active DBMS_STATS session:
SELECT inst_id,
sid,
serial#,
username,
module,
program,
sql_id,
status,
NVL(event, 'ON CPU') AS event
FROM gv$session
WHERE UPPER(module) LIKE '%GATHER_SCHEMA_STATS_04%'
OR UPPER(module) LIKE '%DBMS_STATS%'
ORDER BY inst_id, sid;
6. Check job result after completion
SELECT owner,
job_name,
status,
actual_start_date,
run_duration,
error#,
additional_info
FROM dba_scheduler_job_run_details
WHERE owner = 'RDSADM'
AND job_name = 'GATHER_SCHEMA_STATS_04_JOB'
ORDER BY actual_start_date DESC;
7. Check what stats actually ran
SELECT operation,
target,
status,
start_time,
end_time
FROM dba_optstat_operations
WHERE start_time >= SYSTIMESTAMP - INTERVAL '8' HOUR
AND target LIKE '%DB%'
ORDER BY start_time DESC;
How do 3K+ tables run?
This procedure runs at the schema level:
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'DB',
options => 'GATHER AUTO'
);
Oracle internally identifies which objects in the schema have missing or stale statistics and processes them. It does not require you to create one job per table.
Because we used:
options => 'GATHER AUTO'
Oracle should not blindly gather all 3K+ tables every time. It focuses on objects that need stats.
Before running, you can check how many table-level stats are stale or missing:
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SELECT stale_stats,
COUNT(*) AS table_count
FROM dba_tab_statistics
WHERE owner = '_DB'
AND object_type = 'TABLE'
GROUP BY stale_stats
ORDER BY stale_stats;
To see actual stale/missing tables:
SELECT table_name,
stale_stats,
last_analyzed
FROM dba_tab_statistics
WHERE owner = '_DB'
AND object_type = 'TABLE'
AND (stale_stats = 'YES' OR last_analyzed IS NULL)
ORDER BY last_analyzed NULLS FIRST;
Important recommendation
For this schema, do not schedule this job to run during BD4 or high-load windows. Keep it manual or schedule it strictly in a maintenance window._STATS on 3K+ objects can create real load if many objects are stale.
SELECT client_name,
status
FROM dba_autotask_client
WHERE client_name = 'auto optimizer stats collection';
SET SERVEROUTPUT ON
SET FEEDBACK OFF
-- ============================================================
-- STEP 6: Re-enable ONLY jobs that were enabled BEFORE
-- maintenance (reads from your existing backup table)
-- ============================================================
DECLARE
v_count NUMBER := 0;
v_skip NUMBER := 0;
BEGIN
FOR r IN (
SELECT owner,
job_name,
pre_enabled
FROM stats_job_backup
ORDER BY owner, job_name
) LOOP
IF r.pre_enabled != 'TRUE' THEN
v_skip := v_skip + 1;
DBMS_OUTPUT.PUT_LINE(' [SKIP] ' || r.owner || '.' || r.job_name
|| ' (was disabled before maintenance)');
CONTINUE;
END IF;
BEGIN
DBMS_SCHEDULER.ENABLE(
name => r.owner || '.' || r.job_name
);
v_count := v_count + 1;
DBMS_OUTPUT.PUT_LINE(' [ENABLED] ' || r.owner || '.' || r.job_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' [ENABLE FAILED] ' || r.owner || '.'
|| r.job_name || ' - ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Re-enabled: ' || v_count
|| ' | Left disabled: ' || v_skip);
END;
/
-- ============================================================
-- STEP 7: Re-enable Oracle built-in auto optimizer stats
-- ============================================================
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL
);
DBMS_OUTPUT.PUT_LINE('[7] Auto optimizer stats collection ENABLED.');
END;
/
-- ============================================================
-- STEP 8: Final verification
-- ============================================================
SELECT owner,
job_name,
enabled,
state
FROM dba_scheduler_jobs
WHERE UPPER(job_name) LIKE '%STAT%'
OR UPPER(job_action) LIKE '%DBMS_STATS%'
OR UPPER(job_action) LIKE '%GATHER_TABLE_STATS%'
OR UPPER(job_action) LIKE '%GATHER_SCHEMA_STATS%'
OR UPPER(job_action) LIKE '%GATHER_DATABASE_STATS%'
ORDER BY owner, job_name;
SET FEEDBACK ON
---
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL
);
END;
/
BEGIN
DBMS_SCHEDULER.STOP_JOB(
job_name => 'OWNER.JOB_NAME',
force => TRUE
);
END;
/
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL
);
END;
/
## Step 1: See All Stats Jobs and Their Current State
```sql
SELECT owner,
job_name,
enabled,
state
FROM dba_scheduler_jobs
WHERE UPPER(job_name) LIKE '%STAT%'
OR UPPER(job_action) LIKE '%DBMS_STATS%'
OR UPPER(job_action) LIKE '%GATHER_TABLE_STATS%'
OR UPPER(job_action) LIKE '%GATHER_SCHEMA_STATS%'
OR UPPER(job_action) LIKE '%GATHER_DATABASE_STATS%'
ORDER BY owner, job_name;
```
---
## Step 2: Create a Simple Snapshot Table (One Time Only)
```sql
CREATE TABLE maint_job_snapshot AS
SELECT owner,
job_name,
enabled AS pre_enabled,
state AS pre_state,
SYSDATE AS snapshot_time
FROM dba_scheduler_jobs
WHERE UPPER(job_name) LIKE '%STAT%'
OR UPPER(job_action) LIKE '%DBMS_STATS%'
OR UPPER(job_action) LIKE '%GATHER_TABLE_STATS%'
OR UPPER(job_action) LIKE '%GATHER_SCHEMA_STATS%'
OR UPPER(job_action) LIKE '%GATHER_DATABASE_STATS%';
```
> Run this **once at the very beginning**, it is your safety net to restore from.
---
## Step 3: PRE-MAINTENANCE - Disable All ENABLED Jobs
```sql
-- Generate and review BEFORE running
SELECT 'EXEC DBMS_SCHEDULER.DISABLE(name => '''
|| owner || '.' || job_name
|| ''', force => TRUE);' AS disable_cmd
FROM maint_job_snapshot
WHERE pre_enabled = 'TRUE'
ORDER BY owner, job_name;
```
Then run them all at once in a loop:
```sql
BEGIN
FOR j IN (
SELECT owner, job_name
FROM maint_job_snapshot
WHERE pre_enabled = 'TRUE'
)
LOOP
DBMS_SCHEDULER.DISABLE(
name => j.owner || '.' || j.job_name,
force => TRUE
);
DBMS_OUTPUT.PUT_LINE('Disabled: ' || j.owner || '.' || j.job_name);
END LOOP;
END;
/
```
---
## Step 4: Verify All Jobs Are Now Disabled
```sql
SELECT owner,
job_name,
enabled,
state
FROM dba_scheduler_jobs
WHERE UPPER(job_name) LIKE '%STAT%'
OR UPPER(job_action) LIKE '%DBMS_STATS%'
OR UPPER(job_action) LIKE '%GATHER_TABLE_STATS%'
OR UPPER(job_action) LIKE '%GATHER_SCHEMA_STATS%'
OR UPPER(job_action) LIKE '%GATHER_DATABASE_STATS%'
ORDER BY owner, job_name;
```
> All rows should show `enabled = FALSE` before proceeding.
---
## Step 5: POST-MAINTENANCE - Re-Enable Only Jobs That Were ENABLED Before
```sql
BEGIN
FOR j IN (
SELECT owner, job_name
FROM maint_job_snapshot
WHERE pre_enabled = 'TRUE' -- only restore what WAS enabled
)
LOOP
DBMS_SCHEDULER.ENABLE(
name => j.owner || '.' || j.job_name
);
DBMS_OUTPUT.PUT_LINE('Re-enabled: ' || j.owner || '.' || j.job_name);
END LOOP;
END;
/
```
> Jobs that were **already disabled before maintenance** are automatically skipped because they are not in the `WHERE pre_enabled = 'TRUE'` filter.
---
## Step 6: Verify Restore Matches Original State
```sql
SELECT s.owner,
s.job_name,
s.pre_enabled AS was_before,
j.enabled AS is_now,
CASE
WHEN s.pre_enabled = j.enabled THEN 'OK'
ELSE '*** MISMATCH ***'
END AS status
FROM maint_job_snapshot s
JOIN dba_scheduler_jobs j
ON j.owner = s.owner
AND j.job_name = s.job_name
ORDER BY s.owner, s.job_name;
```
---
## Step 7: Cleanup Snapshot After Confirmed Restore
```sql
-- Only drop after you confirm Step 6 shows all OK
DROP TABLE maint_job_snapshot;
```
---
## Full Maintenance Cheat Sheet
| When | What to Run |
|---|---|
| Start of every maintenance | Step 2 (snapshot), Step 3 (disable loop) |
| During maintenance | Step 4 (verify all disabled) |
| End of maintenance | Step 5 (restore loop) |
| After restore | Step 6 (verify match), Step 7 (cleanup) |
====================================
-- ============================================================
-- SCRIPT: Stats Job Maintenance - Disable / Maintain / Restore
-- ============================================================
SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET VERIFY OFF
-- ============================================================
-- STEP 0: One-time table creation (run once, skip after that)
-- ============================================================
BEGIN
EXECUTE IMMEDIATE '
CREATE TABLE stats_job_backup (
owner VARCHAR2(128),
job_name VARCHAR2(128),
pre_enabled VARCHAR2(5),
pre_state VARCHAR2(30),
backup_time DATE,
CONSTRAINT stats_job_bkp_pk PRIMARY KEY (owner, job_name)
)';
DBMS_OUTPUT.PUT_LINE('[0] Backup table created.');
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -955 THEN
DBMS_OUTPUT.PUT_LINE('[0] Backup table already exists, continuing.');
ELSE
RAISE;
END IF;
END;
/
-- ============================================================
-- STEP 1: Snapshot current state of all stats jobs
-- (captures both enabled AND disabled jobs)
-- ============================================================
BEGIN
-- Clear previous backup
DELETE FROM stats_job_backup;
-- Save current real state before we touch anything
INSERT INTO stats_job_backup (
owner,
job_name,
pre_enabled,
pre_state,
backup_time
)
SELECT
owner,
job_name,
enabled,
state,
SYSDATE
FROM dba_scheduler_jobs
WHERE UPPER(job_name) LIKE '%STAT%'
OR UPPER(job_action) LIKE '%DBMS_STATS%'
OR UPPER(job_action) LIKE '%GATHER_TABLE_STATS%'
OR UPPER(job_action) LIKE '%GATHER_SCHEMA_STATS%'
OR UPPER(job_action) LIKE '%GATHER_DATABASE_STATS%';
COMMIT;
DBMS_OUTPUT.PUT_LINE('[1] Snapshot saved: '
|| SQL%ROWCOUNT || ' jobs recorded.');
END;
/
-- ============================================================
-- STEP 2: Disable Oracle built-in auto optimizer stats
-- ============================================================
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL
);
DBMS_OUTPUT.PUT_LINE('[2] Auto optimizer stats collection DISABLED.');
END;
/
-- ============================================================
-- STEP 3: Stop running jobs + Disable only ENABLED stats jobs
-- (already-disabled jobs are safely skipped)
-- ============================================================
DECLARE
v_count NUMBER := 0;
v_skip NUMBER := 0;
BEGIN
FOR r IN (
SELECT b.owner,
b.job_name,
b.pre_enabled,
b.pre_state
FROM stats_job_backup b
ORDER BY b.owner, b.job_name
) LOOP
-- Skip jobs that were already disabled before maintenance
IF r.pre_enabled = 'FALSE' THEN
v_skip := v_skip + 1;
DBMS_OUTPUT.PUT_LINE(' [SKIP] ' || r.owner || '.' || r.job_name
|| ' (was already disabled before maintenance)');
CONTINUE;
END IF;
-- Stop if it was running
IF r.pre_state = 'RUNNING' THEN
BEGIN
DBMS_SCHEDULER.STOP_JOB(
job_name => r.owner || '.' || r.job_name,
force => TRUE
);
DBMS_OUTPUT.PUT_LINE(' [STOPPED] ' || r.owner || '.' || r.job_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' [STOP FAILED] ' || r.owner || '.'
|| r.job_name || ' - ' || SQLERRM);
END;
END IF;
-- Disable the job
BEGIN
DBMS_SCHEDULER.DISABLE(
name => r.owner || '.' || r.job_name,
force => TRUE
);
v_count := v_count + 1;
DBMS_OUTPUT.PUT_LINE(' [DISABLED] ' || r.owner || '.' || r.job_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' [DISABLE FAILED] ' || r.owner || '.'
|| r.job_name || ' - ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('[3] Disabled: ' || v_count
|| ' | Skipped (pre-disabled): ' || v_skip);
END;
/
-- ============================================================
-- STEP 4: >>> PERFORM YOUR MAINTENANCE WORK HERE <<<
-- ============================================================
PROMPT
PROMPT ===============================================
PROMPT >>> INSERT YOUR MAINTENANCE TASKS HERE <<<
PROMPT ===============================================
PROMPT
-- ============================================================
-- STEP 5: Verify all stats jobs are disabled before proceeding
-- ============================================================
SELECT owner,
job_name,
enabled,
state
FROM dba_scheduler_jobs
WHERE UPPER(job_name) LIKE '%STAT%'
OR UPPER(job_action) LIKE '%DBMS_STATS%'
OR UPPER(job_action) LIKE '%GATHER_TABLE_STATS%'
OR UPPER(job_action) LIKE '%GATHER_SCHEMA_STATS%'
OR UPPER(job_action) LIKE '%GATHER_DATABASE_STATS%'
ORDER BY owner, job_name;
-- ============================================================
-- STEP 6: Re-enable ONLY jobs that were enabled BEFORE
-- maintenance. Jobs that were pre-disabled stay off.
-- ============================================================
DECLARE
v_count NUMBER := 0;
v_skip NUMBER := 0;
BEGIN
FOR r IN (
SELECT owner,
job_name,
pre_enabled
FROM stats_job_backup
ORDER BY owner, job_name
) LOOP
-- KEY FIX: Only re-enable jobs that were enabled before maintenance
IF r.pre_enabled != 'TRUE' THEN
v_skip := v_skip + 1;
DBMS_OUTPUT.PUT_LINE(' [SKIP] ' || r.owner || '.' || r.job_name
|| ' (was disabled before maintenance, leaving it off)');
CONTINUE;
END IF;
BEGIN
DBMS_SCHEDULER.ENABLE(
name => r.owner || '.' || r.job_name
);
v_count := v_count + 1;
DBMS_OUTPUT.PUT_LINE(' [ENABLED] ' || r.owner || '.' || r.job_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' [ENABLE FAILED] ' || r.owner || '.'
|| r.job_name || ' - ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('[6] Re-enabled: ' || v_count
|| ' | Left disabled (intentional): ' || v_skip);
END;
/
-- ============================================================
-- STEP 7: Re-enable Oracle built-in auto optimizer stats
-- ============================================================
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL
);
DBMS_OUTPUT.PUT_LINE('[7] Auto optimizer stats collection ENABLED.');
END;
/
-- ============================================================
-- STEP 8: Final verification
-- ============================================================
SELECT owner,
job_name,
enabled,
state
FROM dba_scheduler_jobs
WHERE UPPER(job_name) LIKE '%STAT%'
OR UPPER(job_action) LIKE '%DBMS_STATS%'
OR UPPER(job_action) LIKE '%GATHER_TABLE_STATS%'
OR UPPER(job_action) LIKE '%GATHER_SCHEMA_STATS%'
OR UPPER(job_action) LIKE '%GATHER_DATABASE_STATS%'
ORDER BY owner, job_name;
SET FEEDBACK ON
SET VERIFY ON
PROMPT ============================================
PROMPT Script completed successfully.
PROMPT ============================================
