搬移資料

car_trace_log是用來顯示"即時"車輛所在位置的table, 為了必免它長的太大, 而影響performance, 所以必須定時清理, 將資料搬至car_trace_log_his. car_trace_log與car_trace_log_his暫定長的一模一樣(因為偷懶, 不想先想).

1. 寫一支搬資料的stored procedure

2. 設定它每30分鐘跑起來搬資料(利用DBMS_SCHEDULER)

Stored Procedure

create or replace

PROCEDURE MOVE_CAR_TRACE_LOG_TO_HISTORY AS

cut_date DATE := sysdate -(1/48);

BEGIN

insert into car_trace_log_his(car_no, create_date, latitude, longitude, location, flag)

select car_no, create_date, Y, X, location, flag

from car_trace_log

where create_date <>

delete car_trace_log where create_date <>

commit;

END MOVE_CAR_TRACE_LOG_TO_HISTORY;

Run scheduler:

BEGIN

DBMS_SCHEDULER.CREATE_JOB(job_name =>'SP_Move_car_log_data',

job_type =>'STORED_PROCEDURE',

job_action =>'MOVE_CAR_TRACE_LOG_TO_HISTORY',

start_date => SYSTIMESTAMP,

repeat_interval =>'FREQ=MINUTELY; INTERVAL=30',

enabled=>TRUE);

END;