Oracle scheduler Learning

In Oracle, the management of scheduler is mainly implemented through dbms_scheduler.

Previous work:

Create a user: create user sched identified by oracle;

Grant create session to sched;

Grant scheduler_admin to sched;

Log on to the database using the user hr and perform the following operations:

Create table sched_test (dt date, str varchar2 (32 ));

Create or replace procedure p_scheduler_test
Is
Begin
Insert into hr. sched_test values (sysdate, 'scheddate job test! ');
Commit;
End;
/

Grant the execution permission of the stored procedure p_scheduler_test to the user sched

Grant execute on p_scheduler_test to sched;

 

Create a job

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
Job_name => 'insert _ SCHED_TEST ',
Job_type => 'stored _ PROCEDURE ',
Job_action => 'hr. P_SCHEDULER_TEST ', // The user mode restriction must be added here.
Start_date => sysdate,
Repeat_interval => 'freq = MINUTELY; INTERVAL = 1 ');
END;
/

You can view job attributes in the user_scheduler_jobs table,

View jobs managed by SCHEDULER. You can query jobs in the USER_SCHEDULER_JOB_LOG and USER_SCHEDULER_JOB_RUN_DETAILS views.

Run exec dbms_scheduler.enable ('insert _ SCHED_TEST ') to make the job take effect.

Run exec dbms_scheduler.disable ('insert _ SCHED_TEST ') to invalidate the job.

Run the following command: exec dbms_scheduler.set_attribute ('job _ name', 'job _ attribute', 'new _ value'); Modify the attributes of a job.

Run the job manually: exec dbms_scheduler.run_job ('job _ name ');

Relationship between job, program, and schedule

Program: Specifies the specific content to be done, that is, what to do

Schedule: Specifies the job execution time and frequency, that is, how to do

Job: Specifies the job to be executed.