Wednesday, November 5, 2008

Queries Running in the Database(V$SQL)

some times we want to know what are queries that are getting executed when any process is run..one way of finding it is using the V$Sql..the SID and Serial# can be used to kill the session in case of long running queries
set pagesize 24
set newpage 1
set linesize 125
column sql_text format a100
column user_name format a12
select b.sid, b.serial# ,
substr(b.username,1,12) user_name,
a.sql_text
from v$sql a, v$session b
where a.hash_value = b.sql_hash_value
and sql_text not like '%from v$sql s, v$session u%'
order by b.sid;


SQL> @cur_sql
sid serial user_name sql_text
5 390 apps select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 an d t.bitmapped=0

To kill a session use the below command
ALTER SYSTEM KILL SESSION 'SID, SER#';
alter system kill session '5,390';