Oracle Job Introduction
The main use scenario
periodically perform related operations in the background: save one table's data to another table 0 o'clock every night, 2: Scheduled backup database, etc.
curing says everything is difficult at first, here I simply describe a create a simple job
The steps are as follows:
1. Create a table G_test
CREATE TABLE G_test
(
ID number (),
c_date DATE
)
2. Create a sequence
Create sequence G_seq
MinValue 1
MaxValue 999999999999999999999999999
start with 141
Increment by 1
Cache;
3. Create a stored procedure
Create or Replace procedure Prc_g_test is
begin
INSERT into g_test values (g_seq.nextval,sysdate);
end Prc_g_test;
4. Create Job,
using the Submit () process, the work is properly planned.
This process has five parameters: job, what, next_date, interval, and No_parse.
PROCEDURE Submit (JobOut Binary_ineger,
What in Varchar2,
next_date in date,
interval in varchar2,
no_parse in Booean:=false)
JobThe parameter is the Binary_ineger returned by the submit () procedure. This value is used to uniquely identify a job.
what parameter is the PL/SQL code block that will be executed.
the Next_date parameter indicates when the job will run.
interval parameter When this work will be re-executed.
The no_parse parameter indicates whether this work should be parsed at the time of submission or execution--true
indicates that this PL/SQL code should be parsed the first time it executes,
false indicates that this PL/SQL code should be parsed immediately.
execute the following script in the Command Window window
variable job1 number;
begin
sys.dbms_job.submit (Job = job,
What = ' prc_g_test; ',
next_date = to_date (' 22-10-2008 10:06:41 ', ' dd-mm-yyyy hh24:mi:ss '),
interval = ' sysdate+1/1440 ');--1440 minutes per day, that is, one minute to run the test procedure
commit;
end;
/
----------------------------------------------------------------------------------
in Plsql, my approach is to:
Declare
job number;
begin
sys.dbms_job.submit (Job, ' prc_g_test; ', sysdate, ' sysdate+1/1440 ');
end;
----------------------------------------------------------------------------------
5. View the job created
View related job information
1. Related views
Dba_jobs
All_jobs
User_jobs
dba_jobs_running contains information about the running job.
such as:
SELECT * from Dba_jobs
6.Run Job
Description: The Run () procedure is used to perform a specified work immediately. This procedure receives only one parameter:
sql> begin
2 Dbms_job.run (: Job);
3 end;
4/
----------------------------------------------------------------------------------
in Plsql, my approach is to:
begin
Dbms_job.run (3017);
end;
----------------------------------------------------------------------------------
7.Delete Job
sql> begin
2 Dbms_job.remove (: Job);--:job can be replaced with dba_jobs.job values such as: 1198
3 end;
4/
