Oracle job operations

1. Start SQL * Plus Worksheet

 

Ii. Create a startup job:

2.1 create a startup jobCode

Variable jobno1 Number ;
Begin
Dbms_job.submit (: jobno1, ' Qy_main; ' , Sysdate, ' Trunc (sysdate + 30, '' Mm '' ) + 24 ' );
Dbms_job.run (: jobno1 );
Commit ;
End ;

2.2 create a startup job rule as follows

Variable job Number ;
Begin
Dbms_job.submit (
Job => : Job, /* Automatically generate job_id */
What => ' Create_task_by_plan; ' , /* Process or SQL statement to be executed */
Next_date => Trunc (sysdate + 1 ) + 6 / 24 , /* Initial execution time */
Interval => ' Trunc (sysdate + 1) + 6/24 ' ); /* Execution cycle */

Dbms_job.run (: Job );/*: Job automatically generates job_id*/
Commit;
End;

2.3 precautions for creating and starting a job
(1) Add ":" before the job number when writing the submit statement, for example, jobno1.
(2) Add ";" after the stored procedure name
(3) The first execution time does not need to be expanded "'"

 

Iii. delete a job

Delete a job using code in worksheet.

3.1.1 Delete Job Code

Begin
Dbms_job.remove ( 82 ); /* 83 is the automatically generated job number */
End ;

3.1.2 obtain the job number code

Select Job, last_sec, next_sec From User_jobs; /* Obtain the job number to be deleted based on the retrieved information. */

3.1.3. The rules for deleting a job are as follows:

Begin
Dbms_job.remove (: Job ); /* : Job automatically generates job_id */
End ;

3.2. method 2

You can log on to PL/SQL and find the job you want to delete in the job folder. Right-click and drop to directly Delete the job.

 

4. modify a job

To modify a job, you can modify the next execution time and interval of the job as follows:

4.1. method 1

Modify the next execution time and interval with code in Worksheet

4.1.1 modify the next execution time
4.1.1.1 modify the code of the next execution time

Begin
Dbms_job.next_date ( 82 , Trunc (sysdate) + 1 + 3.5 / 24 );
End ;

4.1.1.2 modify the rules for the next execution time

Begin
Dbms_job.next_date (: Job, next_date ); /* : Job is the job number to be modified, and next_date is the next execution time to be changed. */
End ;

4.1.2 modify job Interval
4.1.2.1 modify the job Interval code

Begin
Dbms_job.interval ( 82 , ' Trunc (sysdate) + 20 + 3.5/24 ' );
End ;

4.1.2.2 modify Time Interval code rules

Begin
Dbms_job.interval (job, interval ); /* : Job is the job number to be modified, and interval is the time interval to be changed. Do not forget the quotation marks. */
End ;

4.1.2.3. Time Interval for Reference

Trunc (sysdate, ' Yyyy ' ) -- Returns the first day of the current year.
Trunc (sysdate, ' Mm ' ) -- Returns the first day of the current month.
Trunc (sysdate, ' D ' ) -- Returns the first day of the current week.
' Trunc (sysdate + 1) ' -- Every day at midnight
' Trunc (sysdate) + (8*60 + 30)/(24*60) ' -- 08:30 every morning

4.2. method 2
You can log on to PL/SQL and find the job you want to modify in the job folder. Right-click Edit and modify the job directly. Save the modification and log on again.