How to find the Long running SQL statements and kill those sesison?

  Summary: in this tutorial, you will learn how to find the Long running SQL statements and kill that session in the oracle database,

• Login to the server 
• Connect the database
• Find the SQL statements using the below queries
• Kill that session

How to find long-running queries?

set lines 1000

set pages 100

col USERNAME for a15

col MODULE for a30

col PROGRAM for a30

 select status,sid,serial#,sql_id,username,program,module,round(last_call_et/60) Mins from v$session where status='ACTIVE'

AND USERNAME NOT IN ('SYS') group by sid,status,serial#,sql_id,username,program,module,round(last_call_et/60)

order by round(last_call_et/60)

/

The Below command will display all active users with Excution timing 

How to check which SQL taking more time?

select hash_value,sql_text from v$sqltext

where address in (

    select sql_address from v$session

    where sid =&sid)

order by piece

/

Enter value for sid: 961

old   4:  where sid =&sid)

new   4:  where sid =961)

HASH_VALUE SQL_TEXT

---------- -------------------------

2985962331 Select * from emp;

3)After reviewing the application team inform you to kill that session:

How to kill that session?

• Need to collect the SID and Serial#,
• After that fire the following command in db level.

Syntax of that alter commands:

ALTER SYSTEM KILL SESSION 'sid,serial#';

ALTER SYSTEM KILL SESSION '961,6396' IMMEDIATE;

                                                Thanks for your time!!