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:
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.m
TRUNC(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.m
TRUNC(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:
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.
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:
- 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
select 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。
select sysdate - 1/24/60 from dual The resulting time is one minute forward of the current time
- Sysdate – a means pushing forward a day.