Oracle DBMS Job and Scheduler

 DBMS Job:     

        The DBMS_JOB package is the older way to schedule jobs in Oracle, still supported in 12c and 19c but considered legacy. It allows you to submit jobs to be run at specified intervals but has limited functionality, such as executing PL/SQL code or calling stored procedures.

DBMS_SCHEDULER:

        Starting from Oracle 10g, DBMS_SCHEDULER was introduced and offers much more powerful and flexible job scheduling features compared to DBMS_JOB. It includes job chaining, job priorities, and event-based scheduling.

Feature	DBMS_JOB	DBMS_SCHEDULER
Introduced	Older (pre-10g, legacy)	Oracle 10g and later (modern)
Flexibility	Basic scheduling (only PL/SQL, interval-based jobs)	Highly flexible (supports PL/SQL, external programs, shell scripts, event-driven jobs, and more)
Job Types	PL/SQL-based only	PL/SQL blocks, external scripts, executable programs, chains (dependent jobs), etc.
Job Priorities	Not supported	Supported (jobs can be assigned different priorities)
Event-Based Jobs	Not supported	Supported (jobs can be triggered by events such as file arrival, database events, etc.)
Logging and History	Minimal logging	Extensive logging (job execution history, logs, and diagnostic reports)
Chained Jobs	Not supported	Supported (can create job chains with dependencies between jobs)
Time Zones	Not supported	Supported (can schedule jobs based on specific time zones)
Integration with External Systems	Not supported	Supports integration with external systems and OS-level tasks
Enterprise Manager Integration	Basic	Full integration with Oracle Enterprise Manager for easy job monitoring and management
Calendar-Based Scheduling	Not as robust	Robust (supports complex schedules such as hourly, daily, weekly, monthly, etc.)

Job Tasks and Their Procedures:

Task	Procedure	Privilege Needed
Create a job	CREATE_JOB or CREATE_JOBS	CREATE JOB or CREATE ANY JOB
Alter a job	SET_ATTRIBUTE or SET_JOB_ATTRIBUTES	ALTER or CREATE ANY JOB or be the owner
Run a job	RUN_JOB	ALTER or CREATE ANY JOB or be the owner
Copy a job	COPY_JOB	ALTER or CREATE ANY JOB or be the owner
Drop a job	DROP_JOB	ALTER or CREATE ANY JOB or be the owner
Stop a job	STOP_JOB	ALTER or CREATE ANY JOB or be the owner
Disable a job	DISABLE	ALTER or CREATE ANY JOB or be the owner
Enable a job	ENABLE	ALTER or CREATE ANY JOB or be the owner

I - Creating a Job (DBMS_SCHEDULER)

1.Create a Job to Insert Data:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name        => 'my_scheduler_job',
job_type        => 'PLSQL_BLOCK',
job_action      => 'BEGIN INSERT INTO job_test_log VALUES (SYSTIMESTAMP); END;',
start_date      => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=5', -- Every 5 minutes
enabled         => TRUE
);
END;
/

2.    Check the Job Status:

SELECT job_name, enabled, state, last_start_date, next_run_date FROM dba_scheduler_jobs WHERE job_name = 'MY_SCHEDULER_JOB';

3.    Verify Job Execution:

SELECT * FROM job_test_log;

4.    Run a Job Immediately:

BEGIN
DBMS_SCHEDULER.RUN_JOB('my_scheduler_job');
END;
/

II - Create Job with a Program and Schedule

Create a program and schedule separately.

1.    a. Create a Program with PLSQL BLOCK:

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name   => 'log_timestamp_program',
program_type   => 'PLSQL_BLOCK',
program_action => 'BEGIN INSERT INTO job_test_log VALUES (SYSTIMESTAMP); END;',
enabled        => TRUE
);
END;
/

b. Create a Program with PLSQL BLOCK:

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name   => 'cleanup_audit_program',
program_type   => 'STORED_PROCEDURE',
program_action => 'delete_old_audit_data',    // 'delete_old_audit_data' is a stored procedure
enabled        => TRUE
);
END;
/

c. Create a Program with PLSQL BLOCK:

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name   => 'backup_table_program',
program_type   => 'EXECUTABLE',
program_action => '/path/to/backup_table.sh',
enabled        => TRUE
);
END;
/

1. Create a Schedule:

BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name   => 'log_schedule',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',  -- Every 5 minutes
start_date      => SYSTIMESTAMP
);
END;
/

2.    Create a Job Using Program and Schedule:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name      => 'program_scheduler_job',
program_name  => 'log_timestamp_program',
schedule_name => 'log_schedule',
enabled       => TRUE
);
END;
/

3. Modify, Disable, and Remove Jobs

Disable a Job:

BEGIN
DBMS_SCHEDULER.DISABLE('program_scheduler_job');
END;
/

Change Job Interval:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'log_schedule',
attribute => 'repeat_interval',
value => 'FREQ=MINUTELY; INTERVAL=10'
);
END;
/

Drop a Job:

BEGIN
DBMS_SCHEDULER.DROP_JOB('program_scheduler_job', force => TRUE);
END;
/

Find Job name, type, and job action:

select job_name, job_type, job_action from dba_scheduler_jobs where owner='MUTHU';

Check Job Running status:

select job_name, status, error# from dba_scheduler_job_run_details where owner='MUTHU';

Find Scheduler Information’s:

select schedule_name, schedule_type, start_date, repeat_interval from dba_scheduler_schedules where owner='MUTHU';

Check Job Logs:

SELECT job_name, status, log_date, error# FROM dba_scheduler_job_log WHERE job_name = 'PROGRAM_SCHEDULER_JOB';

Check Failed Jobs:

SELECT job_name, status, log_date, error# FROM dba_scheduler_job_log WHERE status = 'FAILED';

Views of DBMS_SCHEDULER:

v$scheduler_running_jobs
dba_scheduler_job_args
dba_scheduler_dests
dba_scheduler_job_dests
dba_scheduler_groups
dba_scheduler_notifications
dba_scheduler_programs