Oracle: Remove scheduler jobs in a loop

If there are too much scheduler jobs in an Oracle database, the CJQ process may die unexpectedly. This has nothing to do with job history, not, it’s the number of jobs known to the system. In my experience, the critical number is somewhere around 32.000 in 10.2 64bit. By the way, that’s the solution for this post here.

That’s a PL/SQL script to remove them subsequently, this example focuses on jobs of type ‘ONCE’ and a special user, but it should be no trouble to change the query on dba_scheduler_jobs in a way you need it for your situation.

set serveroutput on;
DECLARE
    ANZAHL NUMBER(20);
    EXECSTRING VARCHAR(200);
    curs INTEGER;
    retu INTEGER;

BEGIN
    dbms_output.enable (1000000);
    ANZAHL:=0;
    FOR X in
    (
    select owner, job_name
    from dba_scheduler_jobs
    where schedule_type='ONCE'
        and OWNER = 'SCOTT'
        and PROGRAM_OWNER='SCOTT'
        and start_date < sysdate -8
    )
    loop
        ANZAHL:=ANZAHL+1;
        EXECSTRING:= X.OWNER||'.'||X.JOB_NAME;
        --dbms_output.put_line(EXECSTRING);
        dbms_scheduler.drop_job(job_name=>X.OWNER||'.'||X.JOB_NAME);
    end loop;
END;
/

Happy cleanup,
Usn




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply