Use dBMS

xiaoxiao2021-04-01  225

Use the DBMS_JOB package to implement the database background process

1 Establish a process of implementing tasks

Established the following process in Schema Manager or SQL Plus

Create or Replace Procedure "Customer". "T_jobtest" asbegin

Update EMP SET ACTIVE = 0 Where active = 1 and Date_Published

END;

2 Add tasks to the task queue

Perform the following Script in SQL Plus

Variable Jobno Number; Begin dbms_Job.Submit (: Jobno, 'T_Jobtest ();', sysdate, 'sysdate 1'); commit; end; this task is executed immediately (sysdate), and execute once every other day ('sysdate 1').

3 Query this task to join the task queue

Perform the following Script in SQL Plus

Select Job, Next_Date, Next_sec, Failures, Broken from User_JOBS;

------------------

DBMS_JOB package introduction

The task in the dispatch task queue is to use the process in the DBMS_JOB package. Use the task queue without special database privileges. Any user who can use these procedures can use task queues.

Process in the Table 8-2 DBMS_JOB package

Procedure Description Described SUBMIT Submits a job to the job queue. Submitted to the task queue a task REMOVE Removes a specified job from the job queue. Delete specified in the task queue task CHANGE Alters a specified job. You can alter the job description, the time at which the job will be run, or the interval between executions of the job. change task WHAT Alters the job description for a specified job. change the designation of the task of the task content NEXT_DATE Alters the next execution time for a specified job. change the designation of the task The next execution time Interval Alters The Interval Between Executions for a specified job. Change the execution time interval of the specified task. Broken Disables JOB EXECUTION. IF a Job Is Marked As Broken, Oracle Does Not Attempt to Execute IT. Doing the Specifies the Task for Run forces a Specified Job To Run. Force the specified task

Submitting a job to the job queUE submits a task to the task queue

To Submit a New Job To the Job Queue, Use The Submit Procedure In The DBMS_JOB PACKAGE:

DBMS_Job.Submit (Job Out Binary_INTEGER,

What in varchar2,

Next_date in date default sysdate, interval in varchar2 default 'null',

NO_PARSE IN BOOLEAN Default False

The Submit Procedure Returns The Number of The Job You Submitted. Table 8-3 Describes The Procedure's Parameters.

Parameters for Table 8-3 dbms_Job.Submit

Parameter Description job This is the identifier assigned to the job you created. You must use the job number whenever you want to alter or remove the job. For more information about job numbers, see "Job Numbers". What

This is the PL / SQL CODE You Want to Have Executed. Here is the PL / SQL code for you want to do, for more information about defining a job, see "job definitions". Next_date

THIS I 's NEXT DATE WHEN THE JOB WILL BE Run. The default value is sysdate. Interval

This is the date function that calculates the next time to execute the job. The default value is NULL. INTERVAL must evaluate to a future point in time or NULL. For more information on how to specify an execution interval, see "Job Execution Interval" NO_PARSE

This is a flag. The default value is FALSE. If NO_PARSE is set to FALSE (the default), Oracle parses the procedure associated with the job. If NO_PARSE is set to TRUE, Oracle parses the procedure associated with the job the first time that ., pay, you want to strs

As an example, let's submit a new job to the job queue. The job calls the procedure DBMS_DDL.ANALYZE_OBJECT to generate optimizer statistics for the table DQUON.ACCOUNTS. The statistics are based on a sample of half the rows of the ACCOUNTS table. The Job Is Run Every 24 Hours:

Variable Jobno Number;

Begin2> dbms_job.submit (: Jobno,

3> 'DBMS_DDL.Analyze_Object (' 'Table' ",

4> 'DQUON' ',' 'Accounts'',

5> '' Estimate '', NULL, 50); '

6> sysdate, 'sysdate 1');

7> commit;

8> END;

9> /

Statement processed.

Print Jobno

Jobno

------------

14144

Job Definition Task Definition Task Definition is the PL / SQL code specified in the WHAT parameter during the Submit process.

Usually the task definition (content) is a call for a process. This process can have any number of parameters.

Note:

In The Job Definition, Use Two Single Quotation Marks Around Strings. Always Include A Semicolon At The End of The Job Definition. In the task definition, the string is surrounded by 2 single quotes. Task definitions always take a semicolon.

Jobs and Import / Export Jobs Can Be Expoted and Imported.

Thus, if you define a Job in One Database, You Can Transfer It to Another

Database. When Exporting and Importing Jobs, The Job's Number, Environment,

And definition recent unchanged.

The task is to be unloaded (Imported).

Implementation of Job Execution Interval task interval The INTERVAL date function is evaluated immediately before a job is executed. If the job completes successfully, the date calculated from INTERVAL becomes the new NEXT_DATE. If the INTERVAL date function evaluates to NULL and the job completes successfully, THE JOB IS

Deleded from the queue. if a job be per executed periodically at a set interval, use a date expression similar to 'sysdate 7' in the interval

Parameter. for example, if you set the execution interval to 'sysdate 7' on Monday, But for Some Reason (Such as a network failure) The Job Is Not

executed until Thursday, 'SYSDATE 7' then executes every Thursday, not Monday.If you always want to automatically execute a job at a specific time, regardless of the last execution (for example, every Monday), the INTERVAL and NEXT_DATE parameters should Specify A Date Expression Similar To 'Next_Day (Trunc (Sysdate), "Monday")'.

Table 8-5 Lists Some Common Date Expressions Used for Job Execution Interval.

Table 8-5 Common Job Execution Interval

Date Expression Evaluation 'Sysdate 7'

Exactly seven day from the last execution

7 days after the last execution

'Sysdate 1/48'

Every Half Hour

Perform a time every half hour

'Next_day (trunc (tysdate),

'' Monday '') 15/24 '

Every Monday AT 3PM

Each week is one of 3 o'clock in the afternoon

'Next_day (add_months (trunc (sysdate,' 'q' '), 3),

'' Thursday '') '

First Thursday of Each Quarter's first Thursday every quarter

Note:

When specifying next_date or interval

Removing a job from the job queue Removes the task in the task queue

To Remove a Job from The Job Queue, Use The Remove Procedure In The DBMS_JOB PACKAGE:

DBMS_Job.Remove (Job in binary_integer)

The Following Statement Removes Job Number 14144 from the Job Queue:

DBMS_Job.Remove (14144);

SYNTAX for What

You can alter the definition of a job by calling the dbms_job.what procedure. Table 8-3 Describes The Procedure's Parameters.

DBMS_Job.What (Job in Binary_INTEGER,

What in varchar2)

Note: WHEN You Execute Procedure What, Oracle Records Your Current Environment. This Becomes The New Environment for the Job.Syntax for Next_Date

You can alter the next date That Oracle Executes a job by calling the dbms_job.next_date procedure. Table 8-3 Describes The Procedure's Parameters.

DBMS_JOB.NEXT_DATE (Job in Binary_INTEGER,

Next_date in date

Syntax for Interval

You can alter The Execution Interval of a job by calling the dbms_job.interval procedure. Table 8-3 Describes The Procedure's Parameters.

DBMS_Job.Iterval (Job in Binary_INTEGER,

Interval in varchar2)

Viewing Job Queue Information Information Query Task Queue

You can query information in the task queue through the data dictionary view in the table below.

Table 8-6 View of the task queue information

View

Description

DBA_JOBS

Lists all the jobs in the database. Lists tasks in the database

User_jobs

Lists all jobs oowned by the user. Lists the task owned by the user

DBA_JOBS_RUNNING

Lists All Jobs in The Database That Are Currently Running. This View Joins V $ LOCK AND JOB $. Lists the currently running tasks.

For example, you can display information about a job's status and failed executions The following sample query creates a listing of the job number, next execution time, failures, and broken status for each job you have submitted.:

Displays the status of the task and the number of failures.

Select Job, Next_Date, Next_sec, Failures, Broken

From user_jobs;

Job next_date next_sec failies B

---------- ------------------------

9125 01-NOV-94 00:00:00 4 N

14144 24-OCT-94 16:35:35 0 n

41762 01-Jan-00 00:00:00 16 Y

3 rows selected.

You can also display information about jobs currently running The following sample query lists the session identifier, job number, user who submitted the job, and the start times for all currently running jobs:. Information display currently running tasks.

SELECT SID, R.JOB, LOG_USER, R.THIS_DATE, R.THIS_SEC

From dba_jobs_running r, DBA_JOBS J

WHERE R.JOB = J.Job;

SID JOB LOG_USER THIS_DATE THIS_SEC

---------- ------------------------------------- -------

12 14144 JFEE 24-OCT-94 17:21:24

25 8536 Scott 24-Oct-94 16:45:12

2 rows selected.

转载请注明原文地址:https://www.9cbs.com/read-131179.html

New Post(0)