Oracle Job Management __oracle

Svrmgr> select * from Dba_jobs;

Initialize related parameters job_queue_processes
Alter system set JOB_QUEUE_PROCESSES=39 scope=spfile;//maximum value cannot exceed 1000 job_queue_interval = 10//Schedule Job Refresh frequency seconds
Dba_jobs describes all JOBS in the database.
User_jobs describes all JOBS owned by the current user

1 Select Job,what,to_char (last_date, ' yyyy-mm-dd HH24:mi:ss '), To_char (Next_date, ' yyyy-mm-dd hh24:m), interval from DBA _jobs where job in (325,295)
2 Select Job,what,last_date,next_date,interval from Dba_jobs where job in (1,3);

Query the job's condition.
Show Paramter background_dump_dest.
Look at Alter.log and trace.

Svrmgr> select * from Dba_jobs;

Initialize related parameters job_queue_processes
Alter system set JOB_QUEUE_PROCESSES=39 scope=spfile;//maximum value cannot exceed 1000

Job_queue_interval = 10//Dispatch job Refresh frequency second unit

Dba_jobs describes all JOBS in the database.
User_jobs describes all JOBS owned by the current user

1 Select Job,what,to_char (last_date, ' yyyy-mm-dd HH24:mi:ss '), To_char (Next_date, ' yyyy-mm-dd hh24:m), interval from DBA _jobs where job in (325,295)
2 Select Job,what,last_date,next_date,interval from Dba_jobs where job in (1,3);

Query the job's condition.
Show Paramter background_dump_dest.
Look at Alter.log and trace.

How would I stop a job
sql> exec Dbms_job.broken (1,true)

The PL/SQL process has completed successfully.
Sql>commit//must be submitted otherwise invalid

Start Job
sql> exec Dbms_job.broken (1,false)

The PL/SQL process has completed successfully.

Stop another user's job
Sql>exec Sys.dbms_ijob.broken (98,true);
sql>commit;

============================

EXEC Dbms_job.broken (: Job) stopped
EXEC Dbms_job.broken (186,true)//Mark bit broken
EXEC Dbms_job.broken (186,false)//marked as non broken
EXEC Dbms_job.broken (186,false,next_day (sysdate, ' Monday '))//marked as not broken, specifying execution time
EXEC dbms_job.remove (: Job);
EXEC dbms_job.remove (186);
Commit

To rerun a broken job

Third, view related job information
1. Related view
Dba_jobs
All_jobs
User_jobs
Dba_jobs_running contains information about running job

Create Job
Variable jobno number;
Begin
Dbms_job.submit (: Jobno, ' statspack.snap; ', trunc (sysdate+1/24, ' hh '), ' trunc (sysdate+1/24, ' hh ') ', TRUE,: Instno);
Commit
End
Print Jobno

For example, we have established a stored procedure with the name My_job, logged in as the Scott user in Sql/plus, and executed the following command:
sql> variable n number;
Sql> begin
Dbms_job.submit (: n ' my_job; ', Sysdate,
' sysdate+1/360 ');
Commit
End
Sql> print:n;

The system prompts for a successful execution.
Sql> print:n;
The system prints the number of this task, for example, the result is 300.

Simple example
A simple example:

To create a test table
Sql> CREATE TABLE TEST (a date);

Table has been created.

Create a custom procedure
sql> Create or replace procedure MYPROC as
2 begin
3 INSERT into TEST values (sysdate);
4 End;
5/

Procedure has been created.

Create Job
sql> variable JOB1 number;
Sql>
Sql> begin
2 Dbms_job.submit (: Job1, ' MYPROC; ', sysdate, ' sysdate+1/1440 '); --1440 minutes a day, that is, a minute to run the test process once
3 END;
4/

The PL/SQL process has completed successfully.

Run Job
Sql> begin
2 Dbms_job.run (: JOB1);
3 END;
4/

The PL/SQL process has completed successfully.

Sql> Select To_char (A, ' yyyy/mm/dd hh24:mi:ss ') time from TEST;

Time
-------------------
2001/01/07 23:51:21
2001/01/07 23:52:22
2001/01/07 23:53:24

Delete Job
Sql> begin
2 Dbms_job.remove (: JOB1);
3 END;
4/

The PL/SQL process has completed successfully.

=======================================

A, use Dbms_job.run () to execute the job immediately
Sql>begin
Sql>dbms_job.run (: jobno) The job number returned by the Jobno when submitting the submit process
sql>end;
sql>/
B, use Dbms_job.broken () to mark broken again as false
Sql>begin
Sql>dbms_job.broken (: job,false,next_date)
sql>end;
sql>/

========================================
Sql> CREATE Table A (a date);
Table created

Create a procedure
sql> Create or replace procedure test as
2 begin
3 INSERT into a values (sysdate);
4 End;
5/
Procedure created

Submit Job
Sql> Declare
2 JOB1 number; Define a numeric variable
3 begin
4 Dbms_job.submit (: job1, ' Test; ', Sysdate, ' sysdate+1/1440 '); 1440 minutes a day by the minute.
5 end;
6/
Pl/sql procedure successfully completed
Job1
---------
4
Sql> commit;
Commit Complete

Run Job
Sql> begin
2 Dbms_job.run (4);
3 END;
4/
Pl/sql procedure successfully completed

Delete Job
Sql> begin
2 Dbms_job.remove (4);
3 END;
4/
Pl/sql procedure successfully completed
Sql> commit;
Commit Complete

Job change//Modify Job
Execute Dbms_job.change (186,null,null, ' sysdate+3 ');
Execute Dbms_job.change (186, ' Scott.test (update) ');

Dba_jobs
===========================================
field (column) type description
Unique identifier of the job number task
Log_user VARCHAR2 (30) the user who submitted the task
Priv_user VARCHAR2 (30) Users assigned permissions to the task
Schema_user VARCHAR2 (30) User mode for parsing a task
Last_date date the last time the task was successfully run
Last_sec VARCHAR2 (8) such as HH24:MM:SS format last_date date hours, minutes, and seconds
This_date date is running the start time of the task, or null if the task is not running
This_sec VARCHAR2 (8) such as HH24:MM:SS format this_date date hours, minutes, and seconds
Next_date date the next time the task is scheduled to run
Next_sec VARCHAR2 (8) such as HH24:MM:SS format next_date date hours, minutes, and seconds
Total_time number The total time required for the task to run, in seconds
Broken VARCHAR2 (1) flag parameter, y indicates that the task is interrupted and will not run later
INTERVAL VARCHAR2 (200) An expression used to calculate the next run time
Failures number task runs continuously without success
WHAT VARCHAR2 (2000) Pl/sql block to perform a task
Current_session_label RAW MLSLABEL Trusted Oracle Session character for this task
Clearance_hi RAW MLSLABEL Oracle maximum gap that the task can trust
Clearance_lo RAW MLSLABEL Oracle minimum gap that the task can trust
Nls_env VARCHAR2 (2000) The NLS session settings that the task runs
Some other session parameters that the Misc_env RAW (32) task runs

describes interval parameter values
12 o'clock ' TRUNC (sysdate + 1) '
per day 8:30 A.M. ' TRUNC (sysdate + 1) + (8*60+30)/(24*60) '
1 per Tuesday noon 2 points ' Next_day (TRUNC (sysdate), ' Tuesday ') + 12/24 '
Midnight of the first day of the Month 12 o'clock ' TRUNC (Last_day (sysdate) + 1) '
11 o'clock on the last day of each quarter TRUNC (add_months (sysdate + 2/24, 3), ' Q ') -1/24 '
every Saturday and 6:10 A.M. ' TRUNC (Least (Next_day (sysdate, ' SATURDAY '), Next_da Y (Sysdate, "SUNDAY"))) + (6X60+10)/(24x60) '