GATHER STATS

UPDATED SCRIPT:

DECLARE

    v_job_name     VARCHAR2(128) := 'STATS_' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDD_HH24MISS');

    v_program_name VARCHAR2(128) := 'GATHER_STATS_PROG';

BEGIN

    -- PREVENT DUPLICATES: KILL ANY RUNNING STATS JOB FIRST

    FOR rec IN (

        SELECT job_name FROM user_scheduler_jobs

        WHERE job_name LIKE 'STATS_%' AND state = 'RUNNING'

    ) LOOP

        DBMS_SCHEDULER.STOP_JOB(rec.job_name, force => TRUE);

        DBMS_SCHEDULER.DROP_JOB(rec.job_name, force => TRUE);

    END LOOP;

    -- CREATE PROGRAM (idempotent)

    BEGIN

        DBMS_SCHEDULER.CREATE_PROGRAM(

            program_name   => v_program_name,

            program_type   => 'PLSQL_BLOCK',

            program_action => '

                BEGIN

                    DBMS_STATS.GATHER_TABLE_STATS(ownname=>''YOUR_SCHEMA'', tabname=>''TABLE1_NAME'', degree=>32, cascade=>TRUE);

                    DBMS_STATS.GATHER_TABLE_STATS(ownname=>''YOUR_SCHEMA'', tabname=>''TABLE2_NAME'', degree=>32, cascade=>TRUE);

                    DBMS_STATS.GATHER_TABLE_STATS(ownname=>''YOUR_SCHEMA'', tabname=>''TABLE3_NAME'', degree=>32, cascade=>TRUE);

                END;',

            enabled        => TRUE

        );

    EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; END;

    -- CREATE & RUN ONE JOB

    DBMS_SCHEDULER.CREATE_JOB(

        job_name     => v_job_name,

        program_name => v_program_name,

        enabled      => TRUE,

        auto_drop    => TRUE

    );

    DBMS_SCHEDULER.RUN_JOB(v_job_name, use_current_session => FALSE);

    DBMS_OUTPUT.PUT_LINE('ONE JOB STARTED: ' || v_job_name);

END;

/

=========================================================

======

-- =============================================================

-- 1. UNIQUE JOB NAME (NEVER REUSED)

-- =============================================================

DECLARE

    v_job_name     VARCHAR2(128);

    v_program_name VARCHAR2(128) := 'GATHER_STATS_PROG';  -- reusable

BEGIN

    -- Generate unique job name: STATS_YYYYMMDD_HH24MISS

    v_job_name := 'STATS_' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDD_HH24MISS');

    ----------------------------------------------------------------

    -- 2. CLEANUP: Drop only FAILED/BROKEN jobs with same prefix

    ----------------------------------------------------------------

    FOR rec IN (

        SELECT job_name

        FROM user_scheduler_jobs

        WHERE job_name LIKE 'STATS_%'

          AND state IN ('BROKEN', 'FAILED', 'STOPPED')

          AND job_name != v_job_name  -- never touch current

    ) LOOP

        BEGIN

            DBMS_SCHEDULER.DROP_JOB(rec.job_name, force => TRUE);

        EXCEPTION WHEN OTHERS THEN NULL;

        END;

    END LOOP;

    ----------------------------------------------------------------

    -- 3. CREATE REUSABLE PROGRAM (once)

    ----------------------------------------------------------------

    BEGIN

        DBMS_SCHEDULER.CREATE_PROGRAM(

            program_name   => v_program_name,

            program_type   => 'PLSQL_BLOCK',

            program_action => '

                BEGIN

                    DBMS_STATS.GATHER_TABLE_STATS(ownname=>''YOUR_SCHEMA'', tabname=>''TABLE1_NAME'', degree=>32, cascade=>TRUE);

                    DBMS_STATS.GATHER_TABLE_STATS(ownname=>''YOUR_SCHEMA'', tabname=>''TABLE2_NAME'', degree=>32, cascade=>TRUE);

                    DBMS_STATS.GATHER_TABLE_STATS(ownname=>''YOUR_SCHEMA'', tabname=>''TABLE3_NAME'', degree=>32, cascade=>TRUE);

                EXCEPTION WHEN OTHERS THEN RAISE;

                END;',

            enabled        => TRUE,

            comments       => 'Stats gather DEGREE 32'

        );

    EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; END;

    ----------------------------------------------------------------

    -- 4. CREATE AND RUN UNIQUE JOB

    ---------------------------------------

-------------------------

    DBMS_SCHEDULER.CREATE_JOB(

        job_name     => v_job_name,

        program_name => v_program_name,

        enabled      => FALSE,

        auto_drop    => TRUE,

        comments     => 'One-time stats run'

    );

    DBMS_SCHEDULER.SET_ATTRIBUTE(v_job_name, 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);

    DBMS_SCHEDULER.ENABLE(v_job_name);

    DBMS_SCHEDULER.RUN_JOB(v_job_name, use_current_session => FALSE);

    DBMS_OUTPUT.PUT_LINE('Started: ' || v_job_name || ' (DEGREE 32)');

END;

/

-- All recent jobs

SELECT job_name, state, last_start_date, run_duration

FROM user_scheduler_jobs

WHERE job_name LIKE 'STATS_%'

ORDER BY last_start_date DESC;

-- Running

SELECT job_name, elapsed_time FROM all_scheduler_running_jobs

WHERE job_name LIKE 'STATS_%';

-- Last run

SELECT job_name, status, error# FROM all_scheduler_job_run_details

WHERE job_name LIKE 'STATS_%'

ORDER BY log_date DESC FETCH FIRST 5 ROWS ONLY;