What is the difference between dba_jobs and dba_scheduler_jobs
Today, I would like to talk to you about the difference between dba_jobs and dba_scheduler_jobs. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.
Build job in the toad interface. We can query its information through dba_jobs. However, after 10g, there is a new build function dbms_scheduler package create_job () instead of dba_jobs. The built job can be found in dba_scheduler_jobs, and the information is more complex.
The relationship between the two is that dbms_scheduler replaces dba_jobs.
The following describes dbms_scheduler and its parameters
Grant create job to somebody
-- job creation
Begin
Dbms_scheduler.create_job (
Job_name = > 'AGENT_LIQUIDATION_JOB'
Job_type = > 'STORED_PROCEDURE'
Job_action = > 'AGENT_LIQUIDATION.LIQUIDATION',-- stored procedure name
Start_date = > sysdate
Repeat_interval = > 'FREQ=MONTHLY; INTERVAL=1; BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0',-monthly, at intervals of 1 month, on the 1st of each month, at 1 a.m.
Comments = > 'execute Agent inventory procedure'
);
End
/
-- job query
Select owner, job_name, state from dba_scheduler_jobs
Select job_name, state from user_scheduler_jobs
-- all right.
Dbms_scheduler.run_job ('COLA_JOB',TRUE);-- true stands for synchronous execution
-- enable
Dbms_scheduler.enable ('BACKUP_JOB')
-- disabled
Dbms_scheduler.disable ('BACKUP_JOB')
-- stop
Dbms_scheduler.stop_job (job_name = > 'COLA_JOB',force = > TRUE)
-- delete
Dbms_scheduler.drop_job (job_name = > 'COLA_JOB',force = > TRUE);)
Common parameters for calling DBMS_SCHDULER.CREATE_JOB:
Start time (start_time)
Repetition rate (repeat_interval)
End time (end_time)
Job_name: as the name implies, every job must have a name
Schedule_name: if a plan is defined, specify the name of the plan here
Job_type: currently supports three types:
PL/SQL block: PLSQL_BLOCK
Stored procedure: STORED_PROCEDURE
External program: EXECUTABLE (an external program can be a shell script or an operating system-level instruction).
Job_action: job_action has different meanings depending on job_type.
If job_type specifies a stored procedure, you need to specify the name of the stored procedure
If job_type specifies a PL/SQL block, you need to enter the complete PL/SQL code
If the external program specified by job_type, you need to enter the name of script or the instruction name of the operating system
Enabled: as mentioned above, specify whether the job will be activated automatically after it is created.
Comments: a simple description of job
two。 Specify how often the job is executed
10g supports two modes of repeat_interval
The first is the PL/SQL expression, which is also used in the dbms_job package, such as SYSDATE+1, SYSDATE+ 30 Universe 24. 60.
The second is the calendar expression.
For example, MON means Monday, SUN means Sunday, DAY means every day, WEEK means every week, and so on. Here are a few examples of using calendar expressions:
Repeat_interval = > 'FREQ=HOURLY; INTERVAL=2'
Run job every 2 hours
Repeat_interval = > 'FREQ=DAILY'
Run job once a day
Repeat_interval = > 'FREQ=WEEKLY; BYDAY=MON,WED,FRI'
Run job every week on the 1st, 3rd and 5th day.
Repeat_interval = > 'FREQ=YEARLY; BYMONTH=MAR,JUN,SEP,DEC; BYMONTHDAY=30'
Every year, job runs on the 30th of December.
Repeat_interval= > 'FREQ=DAILY;INTERVAL=1;BYHOUR=12'
People who have used crontab should have a sense of deja vu, hehe.
Let's talk about the rules for using calendar expressions:
The calendar expression is basically divided into three parts:
The first part is the frequency, the keyword "FREQ", which must be specified.
The second part is the time interval, that is, the keyword "INTERVAL". The value range is 1-999. It is an optional parameter
The last part is an additional parameter that can be used to specify a date and time precisely, and it is also an optional parameter, for example, the following values are legal:
BYMONTH
BYWEEKNO
BYYEARDAY
BYMONTHDAY
BYDAY
BYHOUR
BYMINUTE
BYSECOND
(for detailed parameters, please refer to the instructions for the use of dbms_scheduler)
Now that you're talking about repeat_interval, you might ask, "is there an easy way to figure out, or evaluate, each run time of job, as well as the next run time?"
The dbms_scheduler package provides a process evaluate_calendar_string that can easily accomplish this requirement. Take a look at the following example:
SQL > set serveroutput on size 999999
SQL > declare
L_start_date TIMESTAMP
L_next_date TIMESTAMP
L_return_date TIMESTAMP
Begin
L_start_date: = trunc (SYSTIMESTAMP)
L_return_date: = l_start_date
For ctr in 1..10 loop
Dbms_scheduler.evaluate_calendar_string (
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15', l_start_date, l_return_date, l_next_date
);
Dbms_output.put_line ('Next Run on:' | |
To_char (lumped nextdate.jmxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxmxmxxxxxxxxxxxxxxxxxxxxmxxxxxxxxxxxxxmxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
);
L_return_date: = l_next_date
End loop
End
/
The output is as follows:
Next Run on: 03/22/2004 07:00:00
Next Run on: 03/22/2004 15:00:00
Next Run on: 03/23/2004 07:00:00
Next Run on: 03/23/2004 15:00:00
Next Run on: 03/24/2004 07:00:00
Next Run on: 03/24/2004 15:00:00
Next Run on: 03/25/2004 07:00:00
-- other systems for SCHEDULER JOB
Select * from dict where table_name like'% SCHEDULER_JOB%'
Mainly refer to DBA_SCHEDULER_JOB_LOG (keep by default for one month) and DBA_SCHEDULER_JOB_RUN_DETAILS
After reading the above, do you have any further understanding of the difference between dba_jobs and dba_scheduler_jobs? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.
