Efficient Tips to get rid of Oracle Long Running Queries!!

Page: 1 2

Killing Oracle Sessions:

Now you will kill all Oracle sessions associated with the Long Running Queries. Use below query to kill such sessions.

select 'ALTER SYSTEM KILL SESSION ''' s.sid','s.serial#''';'
from dba_jobs dj, dba_jobs_running djr, v$process p , v$session s
where dj.job = djr.job
and p.addr = s.paddr
and djr.sid = s.sid;

Killing Operating System Sessions:

You speed up the process of cleaning the killed session undo data by speeding up PMON. PMON can be speeded up by killing the operating system sessions. This is an optional step.

select 'Kill -9 ' p.spid
from dba_jobs dj, dba_jobs_running djr, v$process p , v$session s
where dj.job = djr.job
and p.addr = s.paddr
and djr.sid = s.sid;

V$SESSION view:

Now you have to wait until all the sessions marked as ‘killed' have been really killed and disappear from the V$SESSION view.

Mark the jobs as Unbroken:

Once all sessions marked as ‘killed' are killed and disappear from V$SESSION view then you will mark all the jobs as unbroken using the below query.

select 'Kill -9 ' p.spid
from dba_jobs dj, dba_jobs_running djr, v$process p , v$session s
where dj.job = djr.job
and p.addr = s.paddr
and djr.sid = s.sid;

Running the Jobs:

Once all the Long Running Queries have disappeared then you need to put the jobs back in the job queue and run them.

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

ALL_JOBS views:

You can ensure that all the jobs are back in action by querying the ALL_JOBS view. Read Again!!

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   The power of Oracle Diagnostic Tools!
   DBA Tips for Troubleshooting Oracle Orphaned Sessions!!
   Tricky Oracle Recovery for missing Archive Log!!
   DBA Tips for Oracle 10g Lost SYSOPER Password!!