|
沙发
楼主 |
发表于 2020-5-26 17:54:28
|
只看该作者
查询数据库job语句
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
col owner for a20
col job_name for a30
col LAST_START_DATE for a20
col NEXT_RUN_DATE for a20
col job_action for a40
col what for a40
col status for a10
set linesize 200
set pages 100
select * from (with a as
(select log_Date, job_name, status
from ALL_SCHEDULER_JOB_LOG l
where owner not in ('SYS','SYSTEM','SYSMAN')),
b as
(select job_name, max(log_date) m_date
from ALL_SCHEDULER_JOB_LOG l
where owner not in ('SYS','SYSTEM','SYSMAN')
group by job_name),
c as
(select j.owner,
j.job_name,
j.last_start_date,
j.next_run_date,
j.JOB_ACTION
from all_scheduler_jobs j
where j.owner not in ('SYS','SYSTEM','SYSMAN'))
select c.owner,
a.job_name,
to_char(c.LAST_START_DATE, 'yyyy-mm-dd hh24:mi:ss') LAST_START_DATE,
to_char(c.NEXT_RUN_DATE, 'yyyy-mm-dd hh24:mi:ss') NEXT_RUN_DATE,
c.job_action,
a.status,
sysdate "search_date"
from a, b, c
where a.job_name = b.job_name
and a.job_name = c.job_name
and a.log_date = b.m_date)
union all
select schema_user,
to_char(job) "job_name",
to_char(last_date, 'yyyy-mm-dd hh24:mi:ss') "LAST_DATE",
to_char(next_date, 'yyyy-mm-dd hh24:mi:ss') "NEXT_DATE",
what,
case
when failures = 0 then
'SUCCEEDED'
else
'FAILED'
end case,
sysdate
from dba_jobs
where schema_user not in ('SYS','SYSTEM','SYSMAN') and broken = 'N'; |
|