Technorama

Use the following query to take a look at oracle jobs that have run in a scheduler

SELECT *
FROM dba_scheduler_job_log
WHERE TRUNC(log_date) = TRUNC(SYSDATE)
AND job_name = ''


The following query helps to monitor currently running job status:


SELECT *
FROM dba_scheduler_jobs
WHERE job_name = '';


The following query is helpful in figuring out if any objects have been locked during script execution:


select o.object_name, o.subobject_name, o.object_type, l.*
from gv$locked_object l, dba_objects o
where l.object_id = o.object_id;


Another query that can be used to figure out if there is a “deadlock” kind scenario in the database, i.e two or more processes vying for the same resources..


select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2