Page: 1 2

Every database administrator has to deal with the frustrating situation of dealing with Long Running Queries. We often see database administrators pulling their hair and bouncing their database to get rid of stubborn jobs.

In this article I will give effective tip on removing the Long Running Queries without bouncing your database.

Killing Long Running Queries:

Below diagram shows the simple steps that make your life easy by helping you get rid of Long Running Queries.

Getting information on Long Running Queries:

You can use below query to get a detailed information on Long Running Queries.

select djr.Sid,
dj.Log_User,
dj.Job,
dj.Broken,
dj.Failures,
dj.Last_Date,
dj.Last_Sec,
dj.This_Date,
dj.This_Sec,
dj.Next_Date,
dj.Next_Sec,
dj.Interval,
dj.What
from Dba_Jobs dj,
Dba_Jobs_Running djr
where dj.job = djr.job;

Marking Jobs as BROKEN:

Below script can help you mark all the Long Running Queries as broken.

select ‘Exec DBMS_JOB.BROKEN(‘j.job ‘,TRUE);'
from dba_jobs dj,
dba_jobs_running djr
where dj.job = djr.job; Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Amazing Tips for ORACLE_HOME Issues: Must See!!
   Effective Tips for Materialized Views!!
   The Power of Oracle Virtual Column!!
   Beware of Failed Refreshes, Troubleshoot Broken Links!!