Oracle Database - Job Scheduler

  • Oracle Scheduler (the Scheduler) is implemented by the procedures and functions in the DBMS_SCHEDULER PL/SQL package
  • The Scheduler enables you to control when and where various computing tasks take place in the enterprise environment
  • DBMS_SCHEDULER is a package used to schedule the Job
  • SYS objects scheduled by sys schema only
  • Several of the procedures listed by comma-delimited
  • Oracle allow to schedule a job into DBMS_SCHEDULER
  • Oracle 10g introduced a comprehensive scheduler (DBMS_SCHEDULER) to replace and extend the functionality provided by the DBMS_JOB package
It's Scripting Language can be executing dbms_scheduler, 10g feature.

Types Of Job Scheduler :
  • plsql block
  • stored procedure
DBMS_SCHEDULER is more powerful, has the following advantages
  1. Logging of job runs (job history) --Job operation log
  2. Simple but powerful scheduling syntax (similar to but more powerful than cron syntax) -- More simple and powerful syntax
  3. Running of jobs outside of the database on the operating system (see below)--Run the OS command
  4. Resource management between different classes of jobs-- resource management function
  5. Use of job arguments including passing of objects into stored procedures-- task parameters
  6. Privilege-based security model for jobs -- a better access control
  7. Naming of jobs and comments in jobs --job name and description
  8. Stored, Reusable schedules - storage and reuse of stored procedure

PRIVILEGE DBMS_SCHEDULER :
  • Create any jobs
  • Create External jobs
  • Create job
  • Execute any class
  • Execute any program
  • Manage scheduler

VIEWS OF DBMS_SCHEDULER
  • desc dba_scheduler_schedulers
  • desc dba_scheduler_jobs
  • desc dba_scheduler_running_jobs

	
ParameterDescription

 	
JOB_NAMEName of the Job

	
JOB_TYPEJob  Action Type  (PLSQL_BLOCK, EXCUTABLE, 
EXTERNAL_SCRIPT, STORED_PROCEDURE)

	
JOB_ACTIONAction of the Job. Plsql_block/external 
excutable script /stored procedure name)

	
START_DATEStart date and time of the job

	
REPEAT_INTERVALInline time based schedule

	
END_DATEEnd date and time of the job

	
ENABLEDWhether the job should be enabled immediate or not

	
COMMENTSComments on the job

GRANT PRIVILEGE TO PUBLIC SCHEMA

	conn / as sysdba
	grant create job to public;
	grant create any job to public;

PLSQL BLOCK METHOD

STEP 1 : Create sample table for job schedule 
	SQL> create table tesdb_schedule (id int,name varchar2(20));
	Table created.

STEP 2 : Create sequence for job schedule 
	SQL> create sequence sjs start with 1 increment by 1;
	Sequence created.

STEP 3 : To Check the table is empty 
	SQL> SELECT * FROM tesdb_schedule;
	no rows selected

STEP 4 : Schedule a Job 
   begin
  	  -- Job defined entirely by the create job procedure.
  	  dbms_scheduler.create_job (
  	 job_name => 'Testing',
  	  job_type => 'PLSQL_BLOCK',
  	  job_action => 'BEGIN insert into tesdb_schedule values(sjs.nextval,''CENTER''); END;',
  	  start_date => SYSTIMESTAMP,
  	  repeat_interval => 'freq=minutely',
  	  end_date => null,
 	 enabled => true,
  comments => 'Job defined by inserting records automatically.');
 	end;
/

STEP 5 : To Check Schedule Name 
	SQL>  select JOB_NAME,JOB_TYPE,JOB_ACTION,REPEAT_INTERVAL,START_DATE from dba_scheduler_jobs where job_name='TESTING'





	JOB_NAME
	JOB_TYPE
	JOB_ACTION
	REPEAT_INTERVAL
	START_DATE




	TESTING
	PLSQL_BLOCK
	BEGIN insert into
tesdb_schedule values
sjs.nextval,'CENTER'); END		
	freq=minutely
	27-OCT-23
06.41.52.456115 AM
-04:00

STEP 6 : To Check Table values now. It gets automatically inserted because of job schedule 
	SQL> select * from tesdb_schedule;

		ID 			NAME
		-----	--------------
		 1 			CENTER
		 2 			CENTER

	2 rows selected.

STEP 7 : Run Manual Scheduler 
	SQL >exec dbms_scheduler.run_job('TESTING');

STEP 8 : To Check Table Values now.It gets automatically inserted because of job schedule 

	SQL> select * from tesdb_schedule;

		ID 				NAME
	---------- 	--------------------
		1 				CENTER
		2 				CENTER
		3 				CENTER
		4 				CENTER
		5 				CENTER
STEP 9 : Disable Scheduler 
	BEGIN
	DBMS_SCHEDULER.DISABLE('TESTING');
	END;
	/
	SQL> select JOB_NAME,JOB_TYPE,JOB_ACTION,REPEAT_INTERVAL,START_DATE,enabled,RUN_COUNT
	from dba_scheduler_jobs where job_name='TESTING'?




	

		JOB_NAME
		JOB_TYPE
		JOB_ACTION
		REPEAT_INTERVAL
		START_DATE
		ENABLED
		RUN_COUNT
	

	

		TESTING
		PLSQL_BLOCK
		BEGIN insert into
 tesdb_schedule values
(sjs.nextval,'CENTER'); END;		
		freq=minutely 
		27-OCT-23 
06.41.52.456115 AM
 -04:00		
		FALSE
		15

STEP 10 : Enable Scheduler 
	BEGIN
	DBMS_SCHEDULER.ENABLE('TESTING');
	END;
	/
	FInd the job details
	SQL> select JOB_NAME,JOB_TYPE,JOB_ACTION,REPEAT_INTERVAL,START_DATE,enabled,RUN_COUNT
	from dba_scheduler_jobs where job_name='TESTING'?



	

		JOB_NAME
		JOB_TYPE
		JOB_ACTION
		REPEAT_INTERVAL
		START_DATE
		ENABLED
		RUN_COUNT
	

	

		TESTING
		PLSQL_BLOCK
		BEGIN insert into 
tesdb_schedule values
(sjs.nextval,'CENTER'); END;		
		 freq=minutely
		27-OCT-23 
06.41.52.456115 AM -
04:00		
		TRUE
		15

STEP 11 : Drop The Scheduler 
	SQL> begin
	dbms_scheduler.drop_job('TESTING');
	end;
	/
	PL/SQL procedure successfully completed.

	SQL> select JOB_NAME,JOB_TYPE,JOB_ACTION,REPEAT_INTERVAL,START_DATE,enabled,RUN_COUNT
	from dba_scheduler_jobs where job_name='TESTING';

	no rows selected


STORED PROCEDURE
STEP 1 : Create sample table for job schedule 
SQL> create table stored_proc_schedule(id int, name varchar2(20));
Table created.

STEP 2 : Create sequence for job schedule 
SQL> create sequence s2
    start with 1
    increment by 1
    minvalue 1
    maxvalue 100;
Sequence created.

STEP 3 : Create a stored procedure 
SQL>  create or replace procedure p1
    as
    begin
   insert into stored_proc_schedule values(s2.nextval,s2.nextval);
    commit;
    end;
    /
Procedure created.

STEP 4 : To Check the table is empty 
	SQL> select * from stored_proc_schedule;
	no rows selected

STEP 5 : Execute the procedure 
	SQL> exec p1();
	PL/SQL procedure successfully completed.

STEP 6 : Check table values now. It gets automatically inserted because of job schedule 
	SQL> select * from stored_proc_schedule;
		ID	   NAME
	---------- --------------------
		1 	    1

STEP 7 : Execute the procedure 
	SQL> exec p1();
	PL/SQL procedure successfully completed.
STEP 8 : Check table values now. It gets automatically inserted because of job schedule 
	SQL> select * from stored_proc_schedule;

		ID 		NAME
	---------- --------------------
		1 		1
		2 		2

STEP 9 : Create a job 
	SQL> begin
		dbms_scheduler.create_job(
		job_name  => 'tesdb_sp',
		job_type  => 'stored_procedure',
		job_action  => 'p1',
		start_date  => systimestamp,
		repeat_interval  => 'freq=minutely',
		end_date  => null,
		enabled  => true);
	end;
	/
	PL/SQL procedure successfully completed.
	FInd the job details

	SQL> select JOB_NAME,JOB_TYPE,JOB_ACTION,REPEAT_INTERVAL,START_DATE,enabled,RUN_COUNT from dba_scheduler_jobs
		where job_name='TESDB_SP';



	

		JOB_NAME
		JOB_TYPE
		JOB_ACTION
		REPEAT_INTERVAL
		START_DATE
		ENABLED
		RUN_COUNT
	

	

	

		TESDB_SP
		STORED_PROCEDURE
		p1
		freq=minutely
		27-OCT-23 
07.14.07.603174 AM -
04:00		
		TRUE
		1

Step 10 : Force Run 
	exec sys.dbms_scheduler.run_job('tesdb_sp');


(Oracle Database - User Management)