Oracle -- the 24 timing task

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Making making making making making we sometimes such a requirement: Hope database timing can help us to do a thing. For example, we hope the database can regularly to the repository of a table, or some form of data into another summary table. So this time we can through the periodic tasks using the database to achieve such a function.

The following making making making making making us to give a simple example, if we have the following two tables, T1 and T2.

T1 and the corresponding sequence:

create table t1(a1 integer primary key, a2 varchar2(20), a3 varchar2(20), a4 date);

create sequence seq_t1 start with 1 increment by 1;

 

 

T2 and the corresponding sequence:

create table t2(a1 integer primary key, a2 varchar2(20), a3 date);

create sequence seq_t2 start with 1 increment by 1;

Making making making making making we need to table t1 inside A2 and A4 as the T2 inside the A2 and A3 are inserted into the table t2 in the time of day at two in the morning. Periodic task Oracle is timed to perform a task, but what is the specific task also needs us to specify. So first we have to build their own task, the task is usually a stored procedure. Here we establish the following stored procedure:

 

create or replace procedure copy_t1_to_t2 as
  startTime date := trunc(sysdate - 1); - 0 points yesterday
  endTime   date := trunc(sysdate); - 0 today
  cursor t1_cursor is
    select a2, a4
      from t1
     where a4 >= startTime
       and a4 <endTime; - defines a cursor query T1 recorded yesterday
begin
  for t in t1_cursor loop
    insert into t2 (a1, a2, a3) values (seq_t2.nextval, t.a2, t.a4);- the relevant data of T1 inside the plug into the T2
    commit;Insert a record commit a
  end loop;
end;

 

            

 

Making making making making a timed task

 

After making making making making making a good job, we can submit it to the timing task queue Oracle inside, we establish the code as follows:

SQL> var job number;

SQL> begin

  2    dbms_job.submit(

  3          job => :job,

  4          what => 'copy_t1_to_t2;',

  5          next_date => to_date('2013-11-20 02:00:00','yyyy-MM-dd hh24:mi:ss'),

  6          interval => 'trunc(sysdate+1)+2/24'

  7    );

  8    commit;

  9  end;

 10  /

Making making making making making that is executed on the dbms_job package submit so a stored procedure, in the above code which we have designated four parameters: job, what, next_date and interval. Here we introduce four parameters:

            job: Is an output parameter, representing the task number, so we have to define a job variable.

            what: The input parameter, say what needs to be done, remember not to forget that a stored procedure.", ".

            next_date: The input parameters, the type of date, said the next time this task time, here is the first implementation of the task time.

            interval: The input parameters, the character type, literally means intervals between tasks, in fact it is said that the next execution time, but it is constantly changing, so we usually write sysdate+ a time, such as:

            sysdate+1/(24*60): Said every 1 minutes to perform a,

            sysdate+5/(24*60): Said every 5 minutes to perform a,

Making making making making making sysdate+1: making making making said every 1 days to perform a,

            trunc(sysdate+1)+2/24: That day at 2 in the morning to execute a,

 

Making making making making making in the code above, we put the mission submitted to periodic task queue Oracle immediately commit up, so we will regularly tasks being executed. We can view the operation timing tasks from the user_jobs view.

 

Making making making making operation timing task

Making making making making making if we didn't have commit operating at the time of creation, timing task we are not executed. This time we need a manual to trigger timing task execution. Operation code:

begin
  dbms_job.remove(jobNum);
end;
/

 

The job returns making making making making making jobNum was before we create timing task when the value of the. After the execution means that we need to run any timing task.

Making making making making remove timing task

Making making making making making remove timing task operation:

 

 

begin
  dbms_job.remove(jobNum);
end;
/

            

Indicates the need to delete any timing task.

 

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Colbert at December 15, 2013 - 10:02 PM