SelectSysdate--2016-04-14 16:57:09 Current Time
, Sysdate+1--2016-04-15 16:57:09 Current time of the second day
, TRUNC (sysdate)--2016-04-14 Current Date
, TRUNC (sysdate)+1--The second day of 2016-04-15
, Trunc (sysdate)+ at/ ---2016-04-14 23:00:00 this evening 23 o'clock
, Trunc (Next_day (Sysdate,'Saturday'))+ at/ ---2016-04-16 23:00:00 Next Saturday
, Trunc (Next_day (Sysdate,'Tuesday'))+ at/ ---2016-04-19 23:00:00 Next Tuesday
fromDual
Oracle job creation, scheduled execution of tasks
begin
Sys.dbms_job.submit (Job=: Job,
What='Check_err;',
Next_date=Trunc (Sysdate)+ at/ -,
Interval='trunc (Next_day (Sysdate,"'Friday"')) +23/24');
End;
which
Job: The system automatically generates numbers.
What:check_err a process.
Next_date: Sets the next execution time. This is 23:00 this evening.
Interval: Sets the time interval and how often it is executed. This is the Friday night of the week 23:00
The function Next_day returns the date containing the specified character, the trunc function removes the date time, that is, the day of the 00:00, the time is in days as a unit, so to get such a point, such points, you need to score:
1/24 an hour;
1/1440 a point;
1/3600 a second;
--Learning job in 1.plsql
--Learning Job
--Build Table
CreateTableTest_job (para_date date);
Commit;
Insert intoTest_jobValues(sysdate);
Commit;
Select* fromTest_job;
truncateTableTest_job
--establish the storage process
CreateorReplace procedureTest_jobproce as
begin
Insert intoTest_jobValues(sysdate);
EndTest_jobproce;
--Create Job
--default is performed after the job is established
DeclareTest_job_really Number;
begin
Dbms_job.submit (test_job_really,'Test_jobproce;', Sysdate,'sysdate+1/1440');
Commit;
End;
---Stop Job 25 is an established job test_job_really
begin
Dbms_job.broken (366, true);
Commit;
End;
--Manual Job Execution
begin
Dbms_job.run (366);
Commit;
End;
--Delete Job
begin
Dbms_job.remove (366);
Commit;
End;
--View Execution Results
Select* fromTest_jobOrder byTest_job.para_datedesc;
--View Job
Select* fromSys.user_jobs
--use the following SQL query to see if the job is still running, provided the job execution time cannot be too short
Select* fromDba_jobs_running
--In addition to the submit parameter, the remaining parameters are:
--Run Job
Dbms_job.run (V_job);
--stop a job, inside parameter true also but False,next_date (stop at a certain moment) also is sysdate (stop immediately).
Dbms_job.broke (v_job,true,next_date);
--Delete a job
Dbms_job.remove (V_job);
Dbms_job.what (V_job,'Sp_fact_charge_code;');
--Modify a job name to modify the next run time
Dbms_job.next_date (v_job,sysdate);
About Job run time
1:Execute every Minute
Interval = TRUNC (sysdate, ' mi ') + 1/(24*60)
2:Regular execution every day
Example: Daily 1 o'clock in the morning execution
Interval = TRUNC (sysdate) + 1 +1/(24)
3:regular Weekly Execution
For example: Every Monday 1 o'clock in the morning execution
Interval = TRUNC (Next_day (sysdate, ' Monday ')) +1/24
4:Regular Monthly Execution
For example: 1st 1 o'clock in the morning every month to execute
Interval =>trunc (Last_day (sysdate)) +1+1/24
5:Quarterly Scheduled execution
For example, the first day of each quarter is 1 o'clock in the morning execution
Interval = TRUNC (Add_months (sysdate,3), ' Q ') + 1/24
6:every half-yearly scheduled execution
For example: Every July 1 and January 1 1 o'clock in the morning
Interval = Add_months (trunc (sysdate, ' yyyy '), 6) +1/24
7:scheduled execution every year
For example: January 1 1 o'clock in the morning every year to execute
Interval =>add_months (trunc (sysdate, ' yyyy '), 12) +1/24
Operating frequency settings for the job
1. Daily fixed time operation , such as morning 8:10 minutes: Trunc (sysdate+1) + (8*60+10)/24*60
Available in 2.Toad:
Daily: Trunc (sysdate+1)
Weekly: Trunc (sysdate+7)
Per month: trunc (sysdate+30)
Each Sunday: Next_day (trunc (sysdate), ' Sunday ')
6 points per day: trunc (sysdate+1) +6/24
Half Hour: sysdate+30/(24*60)
3. Run the 15th minute of every hour, for example: 8:15,9:15,10:15 ... : Trunc (sysdate, ' hh ') + (60+15)/(24*60).
Oracle Job Sysdate
