Ryalli's world

Sunday, May 10, 2026

STATS

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 ============================================

Sunday, May 3, 2026

AWR Analysis

SET LINES 260
SET PAGES 200
COLUMN sql_profile FORMAT A45
COLUMN sql_plan_baseline FORMAT A45
COLUMN sql_patch FORMAT A45
COLUMN module FORMAT A35
SELECT inst_id,
sql_id,
child_number,
plan_hash_value,
sql_profile,
sql_plan_baseline,
sql_patch,
module,
parsing_schema_name,
executions,
last_active_time
FROM gv$sql
WHERE sql_id = '7r1gn0tpbwjjj'

ORDER BY last_active_time DESC;

Saturday, April 25, 2026

DEL

batch

Monday, January 19, 2026

Drop

The scripts below are explicitly designed with Heavy Parallelism using DBMS_SCHEDULER.

Tablespace Drops: All 8 tablespaces are dropped simultaneously (8 concurrent threads).
Object Cleanup: All 7 schemas are scrubbed simultaneously (7 concurrent threads).

Here is your Consolidated, Parallel Destruction Runbook.

Parallel Destruction Runbook

Step	Script	Action	Parallelism
1	01_parallel_init.sql	Config & Safety. Sets up control tables and defines the batches.	N/A
2	02_parallel_drop.sql	The Nuke. Launches 8 background jobs to drop tablespaces at the same time.	8x Threads
3	03_wait_blocker.sql	Traffic Cop. Pauses your terminal until all Drop jobs are 100% finished.	N/A
4	04_parallel_sweep.sql	The Sweeper. Launches 7 background jobs to clean schemas at the same time.	7x Threads
5	05_monitor.sql	Verification. Checks status and confirms 0 objects remain.	N/A

SCRIPT 01: Config, Safety & Initialization

Filename: 01_parallel_init.sql

SQL
SET SERVEROUTPUT ON;

DECLARE
    -- [SAFETY VALVE] SET TO TRUE TO ENABLE DESTRUCTION
    v_i_have_backups BOOLEAN := FALSE; 

    v_fallback_ts VARCHAR2(30);
BEGIN
    DBMS_OUTPUT.PUT_LINE('=== STEP 1: PARALLEL INIT & SAFETY ===');

    IF NOT v_i_have_backups THEN
        RAISE_APPLICATION_ERROR(-20000, 'STOP! You must edit Script 01 and set v_i_have_backups := TRUE.');
    END IF;

    -- Detect Fallback TS
    BEGIN
        SELECT property_value INTO v_fallback_ts 
        FROM database_properties WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';
    EXCEPTION WHEN OTHERS THEN v_fallback_ts := 'USERS'; END;

    -- Cleanup Old Controls
    BEGIN EXECUTE IMMEDIATE 'DROP TABLE tablespace_control PURGE'; EXCEPTION WHEN OTHERS THEN NULL; END;
    BEGIN EXECUTE IMMEDIATE 'DROP TABLE tablespace_log PURGE';     EXCEPTION WHEN OTHERS THEN NULL; END;
    BEGIN EXECUTE IMMEDIATE 'DROP TABLE tablespace_config PURGE';  EXCEPTION WHEN OTHERS THEN NULL; END;

    -- Config & Control Tables
    EXECUTE IMMEDIATE 'CREATE TABLE tablespace_config (config_key VARCHAR2(50) PRIMARY KEY, config_value VARCHAR2(500))';
    EXECUTE IMMEDIATE 'INSERT INTO tablespace_config VALUES (''FALLBACK_TS'', :1)' USING v_fallback_ts;

    EXECUTE IMMEDIATE q'[
        CREATE TABLE tablespace_control (
            item_name     VARCHAR2(50) PRIMARY KEY,
            item_type     VARCHAR2(20),
            batch_id      NUMBER,
            status        VARCHAR2(20) DEFAULT 'PENDING',
            error_msg     VARCHAR2(4000),
            CONSTRAINT chk_safe_items CHECK (
                UPPER(item_name) NOT IN ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','USERS') 
            )
        )
    ]';
    
    EXECUTE IMMEDIATE q'[
        CREATE TABLE tablespace_log (
            log_id    NUMBER GENERATED ALWAYS AS IDENTITY,
            log_time  TIMESTAMP DEFAULT SYSTIMESTAMP,
            item_name VARCHAR2(50), 
            action    VARCHAR2(30), 
            status    VARCHAR2(20), 
            message   VARCHAR2(4000)
        )
    ]';

    -- LOAD PARALLEL BATCHES
    INSERT ALL
        -- Batch A: 8 Tablespaces (Will run on 8 threads)
        INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_CORE',      'TABLESPACE', 1)
        INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_INDEX',     'TABLESPACE', 2)
        INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_AUDIT',     'TABLESPACE', 3)
        INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_HISTORY',   'TABLESPACE', 4)
        INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_REPORTING', 'TABLESPACE', 5)
        INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_ARCHIVE',   'TABLESPACE', 6)
        INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_LARGE',     'TABLESPACE', 7)
        INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_RESERVE',   'TABLESPACE', 8)
        
        -- Batch B: 7 Schemas (Will run on 7 threads)
        INTO tablespace_control (item_name, item_type, batch_id) VALUES ('SCHEMA_APP1',        'SCHEMA', 101)
        INTO tablespace_control (item_name, item_type, batch_id) VALUES ('SCHEMA_APP2',        'SCHEMA', 102)
        INTO tablespace_control (item_name, item_type, batch_id) VALUES ('SCHEMA_DATA',        'SCHEMA', 103)
        INTO tablespace_control (item_name, item_type, batch_id) VALUES ('SCHEMA_REPORT',      'SCHEMA', 104)
        INTO tablespace_control (item_name, item_type, batch_id) VALUES ('SCHEMA_AUDIT',       'SCHEMA', 105)
        INTO tablespace_control (item_name, item_type, batch_id) VALUES ('SCHEMA_INTEGRATION', 'SCHEMA', 106)
        INTO tablespace_control (item_name, item_type, batch_id) VALUES ('SCHEMA_ARCHIVE',     'SCHEMA', 107)
    SELECT * FROM dual;
    COMMIT;

    -- Prepare Environment (Kill Sessions)
    FOR r IN (SELECT item_name FROM tablespace_control WHERE item_type = 'SCHEMA') LOOP
        BEGIN
            EXECUTE IMMEDIATE 'ALTER USER ' || r.item_name || ' ACCOUNT LOCK';
            FOR s IN (SELECT sid, serial# FROM v$session WHERE username = r.item_name) LOOP
                EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE';
            END LOOP;
        EXCEPTION WHEN OTHERS THEN NULL; END;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('>> Parallel Batches Configured. Ready to Drop.');
END;
/

SCRIPT 02: Launch Parallel Tablespace Drops

Filename: 02_parallel_drop.sql

SQL
SET SERVEROUTPUT ON;

DECLARE
    v_fallback_ts VARCHAR2(50);
    v_jobs_count  NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('=== STEP 2: LAUNCHING PARALLEL DROPS ===');

    SELECT config_value INTO v_fallback_ts FROM tablespace_config WHERE config_key = 'FALLBACK_TS';

    -- Loop through control table and spawn a job for EACH tablespace immediately
    FOR rec IN (SELECT * FROM tablespace_control WHERE item_type = 'TABLESPACE' ORDER BY batch_id) LOOP
        DBMS_SCHEDULER.CREATE_JOB(
            job_name   => 'JOB_DROP_' || rec.batch_id,
            job_type   => 'PLSQL_BLOCK',
            job_action => q'[
                DECLARE
                    v_ts   VARCHAR2(50) := ']' || rec.item_name || q'[';
                    v_safe VARCHAR2(50) := ']' || v_fallback_ts || q'[';
                    v_cnt  NUMBER;
                BEGIN
                    -- 1. Evacuate Users
                    FOR u IN (SELECT username FROM dba_users WHERE default_tablespace = v_ts) LOOP
                        EXECUTE IMMEDIATE 'ALTER USER ' || u.username || ' DEFAULT TABLESPACE ' || v_safe;
                    END LOOP;

                    -- 2. Drop (The Heavy IO Operation)
                    SELECT count(*) INTO v_cnt FROM dba_tablespaces WHERE tablespace_name = v_ts;
                    IF v_cnt > 0 THEN
                        EXECUTE IMMEDIATE 'DROP TABLESPACE ' || v_ts || ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
                        INSERT INTO tablespace_log (item_name, action, status, message) VALUES (v_ts, 'DROP_TS', 'SUCCESS', 'Dropped via Parallel Job');
                    ELSE
                        INSERT INTO tablespace_log (item_name, action, status, message) VALUES (v_ts, 'DROP_TS', 'SKIPPED', 'Not Found');
                    END IF;
                    
                    UPDATE tablespace_control SET status = 'COMPLETED' WHERE item_name = v_ts;
                    COMMIT;
                EXCEPTION WHEN OTHERS THEN
                    INSERT INTO tablespace_log (item_name, action, status, message) VALUES (v_ts, 'DROP_TS', 'FAILED', SQLERRM);
                    UPDATE tablespace_control SET status = 'FAILED', error_msg = SQLERRM WHERE item_name = v_ts;
                    COMMIT;
                END;
            ]',
            enabled    => TRUE,
            auto_drop  => TRUE
        );
        v_jobs_count := v_jobs_count + 1;
        DBMS_OUTPUT.PUT_LINE('>> Spawned Thread #' || v_jobs_count || ' for: ' || rec.item_name);
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('>> All ' || v_jobs_count || ' threads running. Proceed to Wait Script.');
END;
/

SCRIPT 03: The Blocker (Wait for Completion)

Filename: 03_wait_blocker.sql

SQL
SET SERVEROUTPUT ON;

DECLARE
    v_active_jobs NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('=== WAITING FOR PARALLEL JOBS TO FINISH ===');
    
    LOOP
        SELECT COUNT(*) INTO v_active_jobs 
        FROM dba_scheduler_jobs 
        WHERE job_name LIKE 'JOB_%';
        
        IF v_active_jobs = 0 THEN
            EXIT;
        END IF;
        
        DBMS_OUTPUT.PUT_LINE('... ' || v_active_jobs || ' threads still active ...');
        
        -- Sleep 10s (Universal compatible approach)
        BEGIN
            EXECUTE IMMEDIATE 'BEGIN DBMS_SESSION.SLEEP(10); END;';
        EXCEPTION WHEN OTHERS THEN
            NULL; -- Busy wait for older versions
        END;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('>> ALL THREADS FINISHED.');
END;
/

SCRIPT 04: Launch Parallel Object Sweepers

Filename: 04_parallel_sweep.sql

SQL
SET SERVEROUTPUT ON;

DECLARE
    v_jobs_count NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('=== STEP 4: LAUNCHING PARALLEL SWEEPERS ===');

    FOR rec IN (SELECT * FROM tablespace_control WHERE item_type = 'SCHEMA') LOOP
        DBMS_SCHEDULER.CREATE_JOB(
            job_name   => 'JOB_SWEEP_' || rec.batch_id,
            job_type   => 'PLSQL_BLOCK',
            job_action => q'[
                DECLARE
                    v_owner VARCHAR2(50) := ']' || rec.item_name || q'[';
                BEGIN
                    EXECUTE IMMEDIATE 'ALTER SESSION SET RECYCLEBIN = OFF';
                    
                    -- A. Parallel Schema Cleanup
                    FOR obj IN (
                        SELECT object_name, object_type FROM dba_objects WHERE owner = v_owner
                        AND object_type NOT LIKE 'SYSTEM%' AND object_type NOT LIKE 'LOB%' AND object_name NOT LIKE 'BIN$%'
                        ORDER BY CASE object_type 
                            WHEN 'SYNONYM' THEN 1 WHEN 'VIEW' THEN 2 WHEN 'SEQUENCE' THEN 3
                            WHEN 'PROCEDURE' THEN 4 WHEN 'PACKAGE' THEN 5 WHEN 'TABLE' THEN 6 
                            ELSE 7 END ASC
                    ) LOOP
                        BEGIN
                            IF obj.object_type = 'TABLE' THEN
                                EXECUTE IMMEDIATE 'DROP TABLE "'||v_owner||'"."'||obj.object_name||'" CASCADE CONSTRAINTS PURGE';
                            ELSE
                                EXECUTE IMMEDIATE 'DROP '||obj.object_type||' "'||v_owner||'"."'||obj.object_name||'"' || 
                                                  CASE WHEN obj.object_type = 'TYPE' THEN ' FORCE' ELSE '' END;
                            END IF;
                        EXCEPTION WHEN OTHERS THEN NULL; END;
                    END LOOP;

                    -- B. Parallel Public Synonym Cleanup
                    FOR p IN (SELECT synonym_name FROM dba_synonyms WHERE owner = 'PUBLIC' AND table_owner = v_owner) LOOP
                        BEGIN
                            EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM "' || p.synonym_name || '"';
                        EXCEPTION WHEN OTHERS THEN NULL; END;
                    END LOOP;

                    INSERT INTO tablespace_log (item_name, action, status, message) VALUES (v_owner, 'SWEEP', 'SUCCESS', 'Cleaned via Parallel Job');
                    UPDATE tablespace_control SET status = 'COMPLETED' WHERE item_name = v_owner;
                    COMMIT;
                END;
            ]',
            enabled    => TRUE,
            auto_drop  => TRUE
        );
        v_jobs_count := v_jobs_count + 1;
        DBMS_OUTPUT.PUT_LINE('>> Spawned Thread #' || v_jobs_count || ' for: ' || rec.item_name);
    END LOOP;
END;
/

SCRIPT 05: Status Monitor

Filename: 05_monitor.sql

SQL
SET LINESIZE 200 PAGESIZE 100;
COL item_name FORMAT A30
COL action FORMAT A15
COL status FORMAT A15
COL message FORMAT A60
COL log_time FORMAT A20

PROMPT === FINAL JOB STATUS ===
SELECT job_name, state, run_duration FROM dba_scheduler_jobs WHERE job_name LIKE 'JOB_%';

PROMPT === CONTROL STATUS ===
SELECT item_name, item_type, status, error_msg FROM tablespace_control ORDER BY batch_id;

PROMPT === OBJECT COUNT (Target: 0) ===
SELECT owner, count(*) as remaining_objects 
FROM dba_objects 
WHERE owner IN (SELECT item_name FROM tablespace_control WHERE item_type = 'SCHEMA')
GROUP BY owner;

Ryalli's world

Sunday, May 10, 2026

STATS

1. First verify current schedules

2. Recommended staggered schedule

3. Apply staggered schedules

4. Enable them again

5. Final verification

Important note

1. First confirm the procedure exists

2. Create GATHER_SCHEMA_STATS_04_JOB with schedule

3. Set same attributes as the others

4. Enable it so it follows the schedule

5. Verify 01–07 schedule

6. To manually run only 04 during maintenance

7. Fix broken GATHER_SCHEMA_STATS_01_JOB

1. Pre-check: show only 01–07 stats jobs

2. If GATHER_SCHEMA_STATS_04_JOB is missing, create it

3. Fix the broken GATHER_SCHEMA_STATS_01_JOB

4. During maintenance window: enable only 01–07 jobs

5. Start only these 01–07 jobs in background

6. Monitor running stats jobs

7. After maintenance: disable only 01–07 jobs again

How do 3K+ tables inside 04 schema run?

1. Check whether the procedure exists

2. Create procedure for 04 schema stats

3. Create the scheduler job, disabled initially

4. Run it manually during maintenance window

5. Monitor while running

6. Check job result after completion

7. Check what stats actually ran

How do 3K+ tables run?

Important recommendation

Sunday, May 3, 2026

AWR Analysis

Saturday, April 25, 2026

DEL

Monday, January 19, 2026

Drop

Parallel Destruction Runbook

SCRIPT 01: Config, Safety & Initialization

SCRIPT 02: Launch Parallel Tablespace Drops

SCRIPT 03: The Blocker (Wait for Completion)

SCRIPT 04: Launch Parallel Object Sweepers

SCRIPT 05: Status Monitor

Blog Archive

About Me

2. Create `GATHER_SCHEMA_STATS_04_JOB` with schedule

7. Fix broken `GATHER_SCHEMA_STATS_01_JOB`

2. If `GATHER_SCHEMA_STATS_04_JOB` is missing, create it

3. Fix the broken `GATHER_SCHEMA_STATS_01_JOB`