Database timed Tasks

Timed release lock

Create or replace procedure P_releaselockjob
--Stored procedures for performing operations
Is
Begin
Delete from Pub_t_datalocks t where Locktime < sysdate-1/24; -Handle locks for more than one hour
End P_releaselockjob;
/

Create or replace procedure P_createrlockjob
--Create a stored procedure for a task
Is
--whether the task has been created, if the task has been created, then isexist > 0
Isexist number: = 0;
--Archive Task ID
JobId number;
Begin
Select COUNT (*) into isexist from All_jobs J where j.what= ' p_releaselockjob; ';
--Check whether the task has been created, if it has been created, do not operate
If isexist = 0 Then
--Create a task
Dbms_job.submit (JobId,--mission ID
' P_releaselockjob; ',--the stored procedure to be executed by the task
Sysdate,--Task start time
' sysdate+1/24 '); ----Task execution cycle, specified here once a week, with a 1-hour execution time
Commit

--Running tasks

Dbms_job.run (JOBID);

Commit
End If;
End P_createrlockjob;
/

Create or replace procedure P_closerlockjob
--Deleting a task stored procedure
Is
--whether the task has been created, if the task has been created, then isexist > 0
Isexist number: = 0;
JobId number;
--Archive Task ID
Begin
Select COUNT (*) into isexist from All_jobs J where j.what= ' p_releaselockjob; ';
--Check whether the task has been created, if it has been created, do not operate
If isexist > 0 Then
--Close task

Begin
Select Job into JobId from All_jobs J where j.what= ' p_releaselockjob; ';
Dbms_job.remove (JOBID);
End;

Commit
End If;
End P_closerlockjob;
/

Call P_createrlockjob (); Start a scheduled task
Call P_closerlockjob (); Turn off timed tasks

Database timed Tasks