How to kill Long Running Query using scripts(Version – 2).

How to kill Long Running Query using scripts(Version – 2).

In this article , we will see how to write script to kill running query  on Oracle database.

Aim : To kill Long Running Query

Step 1 :

Create a  table

Step 2 :

Insert huge records using Blocks

The above plsql block will never complete as i gave wrong values .So query will run Long time.

Note : We need one long running query so that i gave  wrong values .In Real time will get email the query which is running  from long time.

QUERY TO FIND LONG RUNNING QUERIES: 

Result :

Step 3 :

Write SQL query in  a file  cat  > LongrunningSess.sql

Long Running sessions information will be sent through Email for initial threshold.(custom threshold)

Long Running session will be killed and it will send email for long running sessions information as well as log of kill commands for Second threshold (custom threshold).

In below example the initial threshold is FIVE MINUTES.

Second threshold will be FIFTEEN MINUTES.

Step 4:

Now write SHELL SCRIPT to call above sql query

Note : The above script will internally  executes  sql query and output will be stored in log file. If you configured mail service you will get mail alert (with logfile).

Step 5 :

Now execute the script with SID after FIVE Minutes.

Now check log file  or Mail.

Note : Before run the script need to check application team about the query. if application team confirmed that query is unusable then only  use script .

Please try in your environment then go for production databases.

Step 6 :

Now execute the script with SID after  FIFTEEN  Minutes.

Now check log file  or Mail.

 

Thank you …

 

 

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment