Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.