Oracle scheduled tasks

Time:2020-10-1

Timing task query

To query Oracle scheduled tasks, you can use:

--Scheduled tasks for all users
		SELECT * FROM dba_jobs;
		--Timing task of the user
select * from user_jobs;

In the query results, the what field generally stores the name of the stored procedure (or the specific stored procedure content). Broken = n indicates that the task is in execution, and broken = y means stop.
To query the contents of a stored procedure according to its name, you can do this by:

select * from user_source where type = 'PROCEDURE'
		and name = 'YXTESTJOB'
order by name, line;

In this way, we can sort out what the timed tasks actually do.

Scheduled task creation

Timing tasks are usually used to execute stored procedures in a fixed time. So create the stored procedure first, and then create the task.
To create a stored procedure:

--Create stored procedure
		create or replace procedure YxtestJob is
		begin
		insert into test1(id, name)
		values(1, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
end;

To create a scheduled task:

--Creating scheduled tasks through scripts
		declare job_no number;
		begin
		sys.dbms_job.submit(job => job_no,
		what => 'YxtestJob;',
		next_date => to_date('19-08-2020 15:53:00', 'dd-mm-yyyy hh24:mi:ss'),
		interval => 'TRUNC(sysdate, ''mi'') + 1/(24*60)');
		commit;
end;

Here, job_ No will be automatically allocated by the database, and the number that has been used will be avoided to prevent duplication.

Note: the value corresponding to what must end with a semicolon. And the corresponding stored procedure must be in normal state
Error report without semicolon end:
Oracle scheduled tasks

Description of setting interval interval

  • Execution per minute:TRUNC(sysdate,'mi') + 1/(24*60)

TRUNC (sysdate, ‘Mi’) indicates that the date is intercepted to minutes, 1 / (2460) means: 1 day / (2460) = 24 hours / (24 * 60) = 1 / 60 hours = 1 minute

  • Hourly execution:TRUNC(sysdate,'hh') + 1/24

TRUNC (sysdate, ‘HH’) indicates that the date is intercepted to an hour, and 1 / 24 means: 1 day / 24 = 24 hours / 24 = 1 hour

  • Every day at 1:00 a.mTRUNC(sysdate + 1) + 1/(24)

TRUNC (sysdate + 1) means the next day, 1 / (24) means 1 hour, the whole meaning means: 1:00 the next day.

  • Every Monday at 1:00 a.mTRUNC(next_day(sysdate, A)) + 1/24Here a can be 1-7 or Monday / mon Sunday / or Monday to Sunday. NLS_ DATE_ When language is in simplified Chinese, Monday can be used.

TRUNC(next_ Day (sysdate, 2)) means next Monday, + 1 / 24 means plus one hour, that is, 1:00 am on Monday.

  • At 1:00 a.m. on the 1st day of each month:TRUNC(LAST_DAY(SYSDATE)) + 1 + 1/24

LAST_ Day (sysdate) indicates the last day of the current month, and TRUNC (last)_ Day (sysdate)) + 1 is the first day of the next month, and + 1 / 24 is 1:00 a.m.

  • At 1:00 a.m. on the first day of each quarter:TRUNC(ADD_MONTHS(SYSDATE, 3),'Q') + 1/24

ADD_ Months (sysdate, 3) is a month of the next quarter, TRUNC (sysdate, ‘Q’) is the 1st day of the first month of this quarter, TRUNC (add)_ Months (sysdate, 3), ‘Q’) means the first month of the next quarter.

  • Regular implementation every year:ADD_MONTHS(trunc(sysdate,'yyyy'), 12) + 1/24

TRUNC (sysdate, ‘yyyy’) means the first day of the year, add_ Months (TRUNC (sysdate, ‘yyyy’), 12) means the first day of the second year. Why is it not direct: TRUNC (sysdate, ‘yyyy’) + 1 / 24, which may be because it is not the first day of the current year when it is created. If it is directly set to the first day of the current year, the time has passed and the scheduled task will not be added to the schedule.

  • Regular implementation every half a year:ADD_MONTHS(trunc(sysdate,'yyyy'), 6) + 1/24

1:00 a.m. on July 1 and January 1 every year.This article is not understood, to be confirmed.

Query NLS_ DATE_ The value of language parameter can be set by:select * from nls_database_parameters

You can also use the PL / SQL tool to create:
Oracle scheduled tasks
Right click jobs = > New = > fill in the corresponding value = > click Apply = > finish
The screenshot here is the editing window, and the next date of 4000 / 1 / 1 indicates that the task has stopped.

Start and stop jobs

After the task is created, it is automatically started. If it is stopped manually, it can be started by script.
Start job: DBMS_ job.run (jobno); — specifies the task number (DBA_ jobs.job Field)
Stop job: dbms.broken (jobno, broken, nextdate); – – broken is a Boolean value
These two statements cannot be executed separately, and they need to be implemented through stored procedures, such as:

--Stop Mission 6
		begin
		dbms_job.broken(6, true, sysdate);
		commit;
end;
--Launch Mission 6
		begin
		dbms_job.run(6);
		commit;
end;

After stopping or starting, you can use the DBA_ jobs.broken Field to see if it takes effect.
Oracle scheduled tasks

The meaning of sysdate – 1 / 24 / 60 in Oracle

In Oracle, sysdate – 1 / 6, sysdate – 1 / 24 / 60, etc. are used to push forward some time from the current time. The calculation method of some time is as follows:

  1. If it is a / B type, then a is the day, B is the hour, which means pushing forward n hours, n = a * 24 / b.

For example: sysdate – 1 / 6, where a = 1, B = 6, n = 1 * 24 / 6 = 4
Namelyselect sysdate - 1/6 from dualThe resulting time is 4 hours ahead of the current time

2. If it is a / B / C type, then a is the day, B is the hour, C is the minute, which means pushing forward for M minutes, M = n60/C, n=A24/B。
Namely select sysdate - 1/24/60 from dual The resulting time is one minute forward of the current time

  1. Sysdate – a means pushing forward a day.

Recommended Today

On the theoretical basis of SRE

What is SRE? When I first got into contact with SRE, many people thought that it was a post with full stack capability in Google and could solve many problems independently. After in-depth exploration, it is found that SRE can solve many problems, but there are too many problems. It is difficult for a post […]