oracle管理job日志保留时长

参考自官网文档

查询job日志

查看Oracle Job的执行日志

select t.owner 用户,
       t.job_name JOB名字,
       t.status 执行状态,
       to_char(t.actual_start_date, 'yyyy-mm-dd hh24:mi:ss') 实际执行时间,
       t.additional_info 附加信息,
       t.error# 错误No
  from dba_scheduler_job_run_details t
 -- where t.job_name = 'My_JOB_NAME'  --job名字
 order by log_date desc;
 
 #可以去掉where条件
 select t.owner 用户,
       t.job_name JOB名字,
       t.status 执行状态,
       to_char(t.actual_start_date, 'yyyy-mm-dd hh24:mi:ss') 实际执行时间,
       t.additional_info 附加信息,
       t.error# 错误No
  from dba_scheduler_job_run_details t
 where t.job_name = 'JOB_AUTO_P_SMS_PARAMVALUES' AND t.status <>'SUCCEEDED'  --job名字
 order by log_date desc;
 
 
SELECT JRD.LOG_ID,
       JRD.JOB_NAME,
       N.JOB_CLASS,
       --TO_CHAR(JRD.ACTUAL_START_DATE, 'YYYY-MM-DD HH24:MI:SS') ACTUAL_START_DATE,
       TO_CHAR(JRD.LOG_DATE, 'YYYY-MM-DD HH24:MI:SS') LOG_DATE,
       JRD.STATUS,
       JRD.ERROR#,
       JRD.RUN_DURATION 运行时长,
       JRD.ADDITIONAL_INFO
  FROM DBA_SCHEDULER_JOB_LOG N, DBA_SCHEDULER_JOB_RUN_DETAILS JRD
 WHERE N.LOG_ID = JRD.LOG_ID
 --  AND N.JOB_NAME = 'JOB_INSERT_SQL_LHR'  --JOB的名称
 ORDER BY JRD.LOG_ID DESC;


 
 
#找到失败job
select *
  from user_scheduler_job_log
 where job_name = 'JOB_GO2ERP_ORDER' AND status <>'SUCCEEDED' ORDER BY log_date desc
 
 #根据log_id找到日志详情
 select * from user_scheduler_job_run_details where log_id = 3954177;

select * from user_scheduler_job_run_details WHERE job_name = 'JOB_GO2ERP_ORDER'AND  status <>'SUCCEEDED' ORDER BY log_date desc

修改job保留时间

默认的只保留30天

#全局修改
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','90');

#指定修改
DBMS_SCHEDULER.SET_ATTRIBUTE('class1','log_history','30');

清理job

#清除所有
DBMS_SCHEDULER.PURGE_LOG();

#清除超过三天的，不包括window log
DBMS_SCHEDULER.PURGE_LOG(log_history => 3, which_log => 'JOB_LOG');


#清除超过三天的，包括window log class2
DBMS_SCHEDULER.PURGE_LOG(log_history => 10, job_name => 'job1, sys.class2');