CREATEORREPLACEPROCEDURE P_CS_ETL_DATA AS
pro_date VARCHAR(32);BEGINselect to_char(sysdate,'yyyy-MM-dd HH24:MI:SS')into pro_date from dual;-- 获取执行时间insertinto A-backup表
select xx from source_table;COMMIT;EXECUTE IMMEDIATE 'RENAME A表 to A-temp表';EXECUTE IMMEDIATE 'RENAME A-backup表 to A表';EXECUTE IMMEDIATE 'RENAME A-temp表 to A-backup表';EXECUTE IMMEDIATE 'TRUNCATE TABLE A-backup表';end;
编写定时任务
DECLARE
job NUMBER;BEGIN
sys.dbms_job.submit(job => job,
what =>'P_CS_ETL_DATA;',
next_date => sysdate +1/(24*60/2),INTERVAL=>'TRUNC(sysdate, ''mi'') + 1/(24*60/2)');--每两分钟执行一次COMMIT;END;-- 然后就能够在user_jobs表中查询到存储过程P_CS_ETL_DATA被调度的情况了;select*from user_jobs where what ='P_CS_ETL_DATA;';