set pagesize 1000
SET LINESIZE 110
col COMP_ID format A9
col COMP_NAME format A40
col STATUS format A15
col VERSION format A12
col owner format a30
col object_name format a30
col object_type format a30
SPOOL DB_DD_CHECK.TXT
/* Database identification */
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;
select * from V$version where banner like 'Oracle Database%';
select INSTANCE_NAME, to_char(STARTUP_TIME,'DD/MM/YYYY HH24:MI:SS') "STARTUP_TIME" from v$instance;
/* Database Component Registry status */
select comp_id, comp_name, status, version from dba_registry;
/* Level of patch in the database */
select * from dba_registry_history;
/* INVALID objects count, by type & in detail */
select count(*) "INVALID Objects Count" from dba_objects where status !='VALID';
select owner, object_type, count(*) from dba_objects where status !='VALID' group by owner, object_type order by owner, object_type;
select owner, object_type, object_name, status from dba_objects where status !='VALID' order by owner, object_type, object_name;
SPOOL OFF
High CPU USAGE
--------------
select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;
Log waiters
----------------
SET LINESIZE 180
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A30
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program,
s.seconds_in_wait
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
AND sid='&a';
SQL text Based on the SID
select sid, sql_text from
v$sqltext t,
v$session s
where
s.sql_address = t.address
and s.sid = '&a'
order by s.sid, piece;
Waiter more than 3600 seconds
set lines 180
column sid format 999999999
column username format a12
column status format a8
column osuser format a10
column process format a10
column wait_class format a20
column wait_time format 999999999
column seconds_in_wait format 999999999
column spid format a10
column pname format a10
column username format a20
column serial# format 999999999
select s.sid, s.serial#, s.username,
s.status, s.osuser,
s.process, s.wait_class,
s.wait_time, s.seconds_in_wait,
p.spid, p.pname, p.username
from v$session s, v$process p
where s.paddr = p.addr
and s.seconds_in_wait > 3600
and (p.pname is null or (p.pname != 'MMNL' and p.pname != 'E000'))
and s.wait_class != 'Idle';
Blockers on the database
----------------------------
column sess format A20
SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess,
id1, id2, lmode, request, type, inst_id
FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER BY id1, request;
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
---------------------------------------------
select b.session_id ,a.SERIAL#, a.username "Blocker Details"
from v$session a,dba_lock b
where b.session_id = a.sid
and b.blocking_others = 'Blocking';
--------------------------------
Based on the OS SPID find the SID,OS user information
col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
uncommited Transactions
set lines 250
column start_time format a20
column sid format 999
column serial# format 999999
column username format a10
column status format a10
column schemaname format a10
column osuser format a10
column process format a10
column machine format a15
column terminal format a10
column program format a25
column module format a10
column logon format a20
prompt ####################################################
prompt # current transactions:
prompt ####################################################
select t.start_time,s.sid,s.serial#,s.username,s.status,s.schemaname,
s.osuser,s.process,s.machine,s.terminal,s.program,s.module,to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
from v$transaction t, v$session s
where s.saddr = t.ses_addr
order by start_time;
If you got the sid it should be easy to find SQL by using the following sql :
select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = &sid
order by piece;
----------------------------------------
col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
------------------------------------
find the user sessions
select substr(s.username,1,20) "User Name",
s.osuser "OS User",
s.status "Status",
lockwait "Lock Wait",
substr(s.program,1,30) "Program",
substr(s.machine,1,15) "Machine",
p.program "Process Program",
si.consistent_gets "Consistent Gets",
s.process "Process PID",
p.spid, p.pid, s.serial#, si.sid
from sys.v_$sess_io si, sys.v_$session s, sys.v_$process p
where s.username is not null and
si.sid(+)=s.sid
and p.addr(+)=s.paddr
order by si.consistent_gets desc
DB link information
--------------------------
COLUMN host FORMAT a18 HEADING "Connect|String"
COLUMN owner FORMAT a8 HEADING "Creator"
COLUMN db_link FORMAT a19 HEADING "DB Link|Name"
COLUMN username FORMAT a8 HEADING "Connect|User"
COLUMN created FORMAT a15 HEADING "Date|Created"
SELECT
host,owner,db_link,username,
to_char(created,'dd-mon-yy hh24:mi') created
FROM
DBA_db_links ORDER BY owner,host;
CLEAR BUFFER
------------
alter system flush buffer_cache;
alter system flush shared_pool;
TABLESPACE LOCATION
set lines 150
column file_name format a80
column MB format 999,999,999
column MAXMB format 999,999,999
select file_name, bytes/1024/1024 MB, autoextensible, maxbytes/1024/1024 MAXMB
from dba_data_files
where tablespace_name = upper('&ts_name');
Tablespace usage
select df.tablespace_name,
df.MB TOTAL,
decode(fs.MB,null,0,fs.MB) FREE,
to_number(to_char((decode(fs.MB,null,0,fs.MB)
*100)/df.MB,'999.99')) PCT_FREE
from
(select tablespace_name, sum(bytes)/1024/1024 MB
from dba_data_files
group by tablespace_name) df
left join
(select tablespace_name, sum(bytes)/1024/1024 MB
from dba_free_space
group by tablespace_name) fs
on df.tablespace_name = fs.tablespace_name
order by 4 asc;
undo usage
set lines 150
select to_char(begin_time,'hh24:mi dd-mon-yyyy') "START",
to_char(end_time,'hh24:mi dd-mon-yyyy') "END",
unxpstealcnt,
expstealcnt,
activeblks,
unexpiredblks,
expiredblks,
tuned_undoretention
from v$undostat
order by end_time;
Total DB size in GB
COLUMN "Total Gb" FORMAT 999,999,999.0
COLUMN "Redo Gb" FORMAT 999,999,999.0
COLUMN "Temp Gb" FORMAT 999,999,999.0
COLUMN "Data Gb" FORMAT 999,999,999.0
Prompt
Prompt "Database Size"
select (select sum(bytes/1073741824) from dba_data_files) "Data Gb",
(select NVL(sum(bytes/1073741824),0) from dba_temp_files) "Temp Gb",
(select sum(bytes/1073741824)*max(members) from v$log) "Redo Gb",
(select sum(bytes/1073741824) from dba_data_files) +
(select NVL(sum(bytes/1073741824),0) from dba_temp_files) +
(select sum(bytes/1073741824)*max(members) from v$log) "Total Gb"
from dual;
ASM usage informaion
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "" of total_mb used_mb on disk_group_name
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
a.name
/
asm_disksreport.sql
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
job_scheduler_report.sql
set feedback off
set echo off
set lines 205
column owner format a15
column job_name format a22
column program_name format a24
column status format a10
column state format a10
column last_start format a25
column last_start_norm format a18
column "DURATION (d:hh:mm:ss)" format a21
column next_run format a25
column next_run_norm format a18
var local_offset number
begin
select extract(timezone_hour from systimestamp) into :local_offset from dual;
end;
/
select dsj.owner,
dsj.job_name,
dsj.program_name,
dsjlmax.status,
dsj.state,
-- to_char(dsj.last_start_date,'dd-mon-yyyy hh24:mi TZH:TZM') last_start,
to_char(dsj.last_start_date + (:local_offset-extract(timezone_hour from dsj.last_start_date))/24,'dd-mon-yyyy hh24:mi') last_start_norm,
extract(day from dsj.last_run_duration) ||':'||
lpad(extract(hour from dsj.last_run_duration),2,'0')||':'||
lpad(extract(minute from dsj.last_run_duration),2,'0')||':'||
lpad(round(extract(second from dsj.last_run_duration)),2,'0') "DURATION (d:hh:mm:ss)",
-- to_char(dsj.next_run_date,'dd-mon-yyyy hh24:mi TZH:TZM') next_run,
to_char(dsj.next_run_date + (:local_offset-extract(timezone_hour from dsj.next_run_date))/24,'dd-mon-yyyy hh24:mi') next_run_norm
from dba_scheduler_jobs dsj,
(select job_name, status
from dba_scheduler_job_log dsjl
where log_date =
(select max(log_date)
from dba_scheduler_job_log dsjl2
where dsjl2.job_name = dsjl.job_name)
) dsjlmax
where dsj.owner not in ('SYS','SYSTEM','EXFSYS','ORACLE_OCM')
and dsj.job_name = dsjlmax.job_name;
SET LINESIZE 110
col COMP_ID format A9
col COMP_NAME format A40
col STATUS format A15
col VERSION format A12
col owner format a30
col object_name format a30
col object_type format a30
SPOOL DB_DD_CHECK.TXT
/* Database identification */
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;
select * from V$version where banner like 'Oracle Database%';
select INSTANCE_NAME, to_char(STARTUP_TIME,'DD/MM/YYYY HH24:MI:SS') "STARTUP_TIME" from v$instance;
/* Database Component Registry status */
select comp_id, comp_name, status, version from dba_registry;
/* Level of patch in the database */
select * from dba_registry_history;
/* INVALID objects count, by type & in detail */
select count(*) "INVALID Objects Count" from dba_objects where status !='VALID';
select owner, object_type, count(*) from dba_objects where status !='VALID' group by owner, object_type order by owner, object_type;
select owner, object_type, object_name, status from dba_objects where status !='VALID' order by owner, object_type, object_name;
SPOOL OFF
High CPU USAGE
--------------
select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;
Log waiters
----------------
SET LINESIZE 180
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A30
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program,
s.seconds_in_wait
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
AND sid='&a';
SQL text Based on the SID
select sid, sql_text from
v$sqltext t,
v$session s
where
s.sql_address = t.address
and s.sid = '&a'
order by s.sid, piece;
Waiter more than 3600 seconds
set lines 180
column sid format 999999999
column username format a12
column status format a8
column osuser format a10
column process format a10
column wait_class format a20
column wait_time format 999999999
column seconds_in_wait format 999999999
column spid format a10
column pname format a10
column username format a20
column serial# format 999999999
select s.sid, s.serial#, s.username,
s.status, s.osuser,
s.process, s.wait_class,
s.wait_time, s.seconds_in_wait,
p.spid, p.pname, p.username
from v$session s, v$process p
where s.paddr = p.addr
and s.seconds_in_wait > 3600
and (p.pname is null or (p.pname != 'MMNL' and p.pname != 'E000'))
and s.wait_class != 'Idle';
Blockers on the database
----------------------------
column sess format A20
SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess,
id1, id2, lmode, request, type, inst_id
FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER BY id1, request;
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
---------------------------------------------
select b.session_id ,a.SERIAL#, a.username "Blocker Details"
from v$session a,dba_lock b
where b.session_id = a.sid
and b.blocking_others = 'Blocking';
--------------------------------
Based on the OS SPID find the SID,OS user information
col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
uncommited Transactions
set lines 250
column start_time format a20
column sid format 999
column serial# format 999999
column username format a10
column status format a10
column schemaname format a10
column osuser format a10
column process format a10
column machine format a15
column terminal format a10
column program format a25
column module format a10
column logon format a20
prompt ####################################################
prompt # current transactions:
prompt ####################################################
select t.start_time,s.sid,s.serial#,s.username,s.status,s.schemaname,
s.osuser,s.process,s.machine,s.terminal,s.program,s.module,to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
from v$transaction t, v$session s
where s.saddr = t.ses_addr
order by start_time;
If you got the sid it should be easy to find SQL by using the following sql :
select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = &sid
order by piece;
----------------------------------------
col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
------------------------------------
find the user sessions
select substr(s.username,1,20) "User Name",
s.osuser "OS User",
s.status "Status",
lockwait "Lock Wait",
substr(s.program,1,30) "Program",
substr(s.machine,1,15) "Machine",
p.program "Process Program",
si.consistent_gets "Consistent Gets",
s.process "Process PID",
p.spid, p.pid, s.serial#, si.sid
from sys.v_$sess_io si, sys.v_$session s, sys.v_$process p
where s.username is not null and
si.sid(+)=s.sid
and p.addr(+)=s.paddr
order by si.consistent_gets desc
DB link information
--------------------------
COLUMN host FORMAT a18 HEADING "Connect|String"
COLUMN owner FORMAT a8 HEADING "Creator"
COLUMN db_link FORMAT a19 HEADING "DB Link|Name"
COLUMN username FORMAT a8 HEADING "Connect|User"
COLUMN created FORMAT a15 HEADING "Date|Created"
SELECT
host,owner,db_link,username,
to_char(created,'dd-mon-yy hh24:mi') created
FROM
DBA_db_links ORDER BY owner,host;
CLEAR BUFFER
------------
alter system flush buffer_cache;
alter system flush shared_pool;
TABLESPACE LOCATION
set lines 150
column file_name format a80
column MB format 999,999,999
column MAXMB format 999,999,999
select file_name, bytes/1024/1024 MB, autoextensible, maxbytes/1024/1024 MAXMB
from dba_data_files
where tablespace_name = upper('&ts_name');
Tablespace usage
select df.tablespace_name,
df.MB TOTAL,
decode(fs.MB,null,0,fs.MB) FREE,
to_number(to_char((decode(fs.MB,null,0,fs.MB)
*100)/df.MB,'999.99')) PCT_FREE
from
(select tablespace_name, sum(bytes)/1024/1024 MB
from dba_data_files
group by tablespace_name) df
left join
(select tablespace_name, sum(bytes)/1024/1024 MB
from dba_free_space
group by tablespace_name) fs
on df.tablespace_name = fs.tablespace_name
order by 4 asc;
undo usage
set lines 150
select to_char(begin_time,'hh24:mi dd-mon-yyyy') "START",
to_char(end_time,'hh24:mi dd-mon-yyyy') "END",
unxpstealcnt,
expstealcnt,
activeblks,
unexpiredblks,
expiredblks,
tuned_undoretention
from v$undostat
order by end_time;
Total DB size in GB
COLUMN "Total Gb" FORMAT 999,999,999.0
COLUMN "Redo Gb" FORMAT 999,999,999.0
COLUMN "Temp Gb" FORMAT 999,999,999.0
COLUMN "Data Gb" FORMAT 999,999,999.0
Prompt
Prompt "Database Size"
select (select sum(bytes/1073741824) from dba_data_files) "Data Gb",
(select NVL(sum(bytes/1073741824),0) from dba_temp_files) "Temp Gb",
(select sum(bytes/1073741824)*max(members) from v$log) "Redo Gb",
(select sum(bytes/1073741824) from dba_data_files) +
(select NVL(sum(bytes/1073741824),0) from dba_temp_files) +
(select sum(bytes/1073741824)*max(members) from v$log) "Total Gb"
from dual;
ASM usage informaion
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "" of total_mb used_mb on disk_group_name
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
a.name
/
asm_disksreport.sql
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
job_scheduler_report.sql
set feedback off
set echo off
set lines 205
column owner format a15
column job_name format a22
column program_name format a24
column status format a10
column state format a10
column last_start format a25
column last_start_norm format a18
column "DURATION (d:hh:mm:ss)" format a21
column next_run format a25
column next_run_norm format a18
var local_offset number
begin
select extract(timezone_hour from systimestamp) into :local_offset from dual;
end;
/
select dsj.owner,
dsj.job_name,
dsj.program_name,
dsjlmax.status,
dsj.state,
-- to_char(dsj.last_start_date,'dd-mon-yyyy hh24:mi TZH:TZM') last_start,
to_char(dsj.last_start_date + (:local_offset-extract(timezone_hour from dsj.last_start_date))/24,'dd-mon-yyyy hh24:mi') last_start_norm,
extract(day from dsj.last_run_duration) ||':'||
lpad(extract(hour from dsj.last_run_duration),2,'0')||':'||
lpad(extract(minute from dsj.last_run_duration),2,'0')||':'||
lpad(round(extract(second from dsj.last_run_duration)),2,'0') "DURATION (d:hh:mm:ss)",
-- to_char(dsj.next_run_date,'dd-mon-yyyy hh24:mi TZH:TZM') next_run,
to_char(dsj.next_run_date + (:local_offset-extract(timezone_hour from dsj.next_run_date))/24,'dd-mon-yyyy hh24:mi') next_run_norm
from dba_scheduler_jobs dsj,
(select job_name, status
from dba_scheduler_job_log dsjl
where log_date =
(select max(log_date)
from dba_scheduler_job_log dsjl2
where dsjl2.job_name = dsjl.job_name)
) dsjlmax
where dsj.owner not in ('SYS','SYSTEM','EXFSYS','ORACLE_OCM')
and dsj.job_name = dsjlmax.job_name;
