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 ============================================
No comments:
Post a Comment