remote dba support
More
    HomeOracle DBA TipsOracle Scheduler Jobs Example

    Oracle Scheduler Jobs Example

    Blog post explains how to configure job scheduler in Oracle by example with how to manage, delete, drop, disable and enable jobs with how to check history of job if broken and enable.

    JOBS CREATION

    One of the most common errors is reflected in the fact that the JOBS execution parameter in the database does not find a value greater than 0, for the above, the following is reviewed:

    - Advertisement -
    dbametrix

    We connect with system or sys via sqlplus:

    NOTE:

    KEEP IN MIND THAT DIFFERENT MODIFICATIONS ON A JOB MUST BE CONFIRMED WITH COMMIT;

    SQL> show parameters job

    - Advertisement -
    dbametrix

    It should show job_queue_processes = n where n must be greater than 0. If it becomes 0 then the following script is executed to modify the parameter:

    SQL> alter system set job_queue_processes = 5;

    We check if they start like this.

    The above explains that:

    job_queue_processes = 5 (5 for saying a value)
    job_queue_interval = 1

    This means that 5 new processes are raised that wake up every minute to see if there are any JOBs to launch.

    – It is recommended to put the name of the JOB to be able to identify its monitoring and follow-up later.

    – Any of the following statements is recommended to create the job.

    It should be noted that there are several types of scheduled tasks in Oracle 11g, one of which is executed on DBMS_SCHEDULER.CREATE_JOB. the DBMS_SHEDULER was implemented as of Oracle10G.

    BEGIN

    DBMS_SCHEDULER.CREATE_JOB (
    job_name => ‘DELETE_LOGS’,
    job_type => ‘EXECUTABLE’,
    job_action => ‘/users/oracle/scripts/delete_logs.sh’,
    repeat_interval => ‘FREQ = DAILY; BYHOUR = 17; BYMINUTE = 26’,
    job_class => ‘”DEFAULT_JOB_CLASS”‘,
    auto_drop => FALSE,
    comments => ‘Delete old files’,
    enabled => TRUE);
    END;
    /

    BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
    job_name => '"UXXIVB". "OCCUPATIONS"',
    job_type => 'STORED_PROCEDURE',
    job_action => 'UXXIVB.PR_OCUPACIONES',
    number_of_arguments => 0,
    start_date => TO_TIMESTAMP_TZ ('2020-01-23 09: 05: 12.000000000 AMERICA / BOGOTA', 'YYYY-MM-DD HH24: MI: SS.FF TZR'),
    repeat_interval => 'FREQ = MINUTELY; INTERVAL = 2',
    end_date => NULL,
    enabled => FALSE,
    auto_drop => FALSE,
    comments => '');
    DBMS_SCHEDULER.SET_ATTRIBUTE (
    name => '"UXXIVB". "OCCUPATIONS"',
    attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
    DBMS_SCHEDULER.enable (
    name => '"UXXIVB". "OCCUPATIONS"');
    END;
    DECLARE
    X NUMBER;

    BEGIN
    SYS.DBMS_JOB.SUBMIT
    (
    job => X
    , what => 'SCHEME.STORED_PROCEDURE;'
    , next_date => to_date ('30 / 11/2020 13:00:30 ',' dd / mm / yyyy hh24: mi: ss')
    , interval => 'TRUNC (SYSDATE + 7)'
    , no_parse => FALSE
    );
    : JobNumber: = to_char (X);
    END;

    – To see the details of the JOB, the following sentence is executed.

    SELECT
    job_name
    , last_start_date
    , last_run_duration
    , next_run_date
    , repeat_interval
    FROM dba_scheduler_jobs
    WHERE job_name = '<job_name>';

    – In the same way, each time the job is executed a log record is saved.

    SELECT
    job_name
    , log_date
    , operation
    ,status
    FROM dba_scheduler_job_log
    WHERE job_name = '<job_name>';

    – To clear all the history of the JOB

    SQL> exec dbms_scheduler.purge_log ();

    – How to Modify a JOB:

    You can modify the attributes of a job using the SET_ATTRIBUTE procedure.

    BEGIN
    dbms_scheduler.set_attribute (
    name => '<job_name>'
    , attribute => 'repeat_interval'
    , value => 'freq = weekly; byday = sun ');
    END;
    /

    – Stop a JOB

    SQL> exec dbms_scheduler.stop_job (job_name => '<job_name>');

    – How to Enable or disable a JOB:

    exec dbms_scheduler.disable ('<job name>');
    exec dbms_scheduler.enable ('<job name>');

    – How to Delete a JOB:

    exec dbms_scheduler.drop_job ('<job_name>');

    – To see the history of the JOB, the following sentence is executed.

    CHQJOBS.SQL
    column job format 99999
    column schema format a15
    column fail format 999
    set pages 100
    set line 200
    select job, substr (SCHEMA_USER, 1,12) schema,
    substr (TO_CHAR (NEXT_DATE, 'DAY DD-MON-YY'), 1,19) Next_Day,
    NEXT_SEC, BROKEN b,
    FAILURES fail, substr (what, 1,15) what,
    decode (SUBSTR (INTERVAL, 30,30), null, SUBSTR (INTERVAL, 1,30),
    SUBSTR (INTERVAL, 30,30)) INTERVAL
    from dba_jobs
    ORDER BY NEXT_DATE, NEXT_SEC
    /

    – To see the query of the INTERVALS of the JOB’s, the following sentence must be executed with the DBA privilege user.

    SQL> SELECT owner, schedule_name, schedule_type, start_date, repeat_interval FROM dba_scheduler_schedules;

    To activate a Job that is in inactive state (BROKEN = ’N’), run the following statement in which the user who owns the JOB is changed.

    Activate them:

    sqlplus '/ as sysdba'
    SET SERVEROUTPUT ON;
    DECLARE
    CURSOR C_JOBS IS
    select job, BROKEN from dba_jobs
    where UPPER (schema_user) = '<SCHEME_USER_IN_CAPITAL_LETTERS>';
    BEGIN
    FOR VREG IN C_JOBS LOOP
    BEGIN
    IF VREG.BROKEN = 'Y' THEN
    dbms_ijob.broken (VREG.JOB, FALSE);
    END IF;
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE (SQLERRM);
    END;
    END LOOP;
    END;
    /

    Disable them:

    sqlplus '/ as sysdba'
    SQL> SET SERVEROUTPUT ON;
    DECLARE
    CURSOR C_JOBS IS
    select job, BROKEN from dba_jobs
    where UPPER (schema_user) = '<SCHEME_USER_IN_CAPITAL_LETTERS>';
    BEGIN
    FOR VREG IN C_JOBS LOOP
    BEGIN
    IF VREG.BROKEN = 'N' THEN
    dbms_ijob.broken (VREG.JOB, TRUE);
    END IF;
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE (SQLERRM);
    END;
    END LOOP;
    END;
    /

    In some cases, when the job is rescheduled, it is necessary to carry out a run to update the rescheduling data.

    BEGIN
    SYS.DBMS_JOB.RUN (job_number);
    COMMIT;
    END;
    /

    When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. These all Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

    - Advertisement -
    dbametrix
    - Advertisment -
    remote dba services

    Most Popular