Create an oracle job

 

Oracle job introduction
Main scenarios
Perform related operations on the background at regular intervals. For example, save the data of one table to another at every night, and 2. Back up the database at regular intervals.

It is difficult to start with everything. Here I will simply describe a simple job
The procedure is as follows:
1. Create a table g_test
Create Table g_test
(
ID number (12 ),
C_date date
)
2. Create a sequence
Create sequence g_seq
Minvalue 1
Max value 999999999999999999999999999
Start with 141
Increment by 1
Cache 20;

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 a job,
When 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 binary_ineger returned by the submit () process. This value uniquely identifies a job.
What parameter is the PL/SQL to be executedCodeBlock.
The next_date parameter specifies when the job will be run.
The interval parameter indicates when the job will be re-executed.
The no_parse parameter indicates whether the job should perform syntax analysis at the time of submission or execution -- true
Indicates that this PL/SQL code should perform syntax analysis when it is executed for the first time,
False indicates that the PL/SQL code should be analyzed immediately.

Execute the following script in the Command 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 + 100'); -- 1/1440 minutes a day, that is, once a minute to run the test process
Commit;
End;
/

----------------------------------------------------------------------------------
In PLSQL, my approach is:

Declare
Job number;
Begin
SYS. dbms_job.submit (job, 'prc _ g_test; ', sysdate, 'sysdate + 100 ');
End;
----------------------------------------------------------------------------------

5. view the created job
View related job information
1. Related views
Dba_jobs
All_jobs
User_jobs
Dba_jobs_running contains information about running jobs.
For example:
Select * From dba_jobs

6,Run job
Note: The run () process is used to immediately execute a specified job. This process only receives one parameter:
SQL> begin
2 dbms_job.run (: Job );
3 end;
4/

----------------------------------------------------------------------------------
In PLSQL, my approach is:
Begin
Dbms_job.execute (3017 );
End;
----------------------------------------------------------------------------------

7,Delete a job
SQL> begin
2 dbms_job.remove (: Job); --: job can be replaced by the value of dba_jobs.job, for example, 1198.
3 end;
4/

Note: You can directly define the variable in the sqlplus command line !!!!