创建存储，定时执行存储

create or replace procedure pro_del_t_reportsn_temp is

begin

--2、每天17点后删除t_reportsn_temp 表前一天所有的数据

delete t_reportsn_temp t

 where t.create_date between

       to_date(to_char(sysdate - 1, 'yyyy-mm-dd') || ' 00:00:00',

               'yyyy-mm-dd hh24:mi:ss') and

     

       to_date(to_char(sysdate - 1, 'yyyy-mm-dd') || ' 23:59:59',

               'yyyy-mm-dd hh24:mi:ss');

            commit;

end pro_del_t_reportsn_temp;

 

begin
  sys.dbms_job.submit(job => :job,
                      what => 'pro_del_t_reportsn_temp;',
                      next_date => to_date('28-07-2020 18:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'trunc(sysdate+1)+18/24');
  commit;
end;

 

一.查all_triggers表得到trigger_name
 
Sql代码
 
select * from all_triggers where table_name='XXX';  
 
二.根据trigger_name查询出触发器详细信息
 
Sql代码  
select * from all_source where type='TRIGGER' and text like '%pro_del_t_reportsn_temp%' AND name='TR_XXX';

查询用户所有定时任务
select * from user_jobs