Find running SQLs and Kill Session

==========find running SQLs
col USERNAME for a20
col SQL_TEXT for a100
col SID for 999999
set lines 300

select sesion.sid,
sesion.serial#,
sesion.username,
sesion.sql_id,
sesion.sql_child_number,
optimizer_mode,
hash_value,
address,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null;


==========find locking sessions

SELECT vh.sid locking_sid,
 vs.status status,
 vs.program program_holding,
 vw.sid waiter_sid,
 vsw.program program_waiting
FROM v$lock vh,
 v$lock vw,
 v$session vs,
 v$session vsw
WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2
 FROM v$lock
 WHERE request = 0
 INTERSECT
 SELECT id1, id2
 FROM v$lock
 WHERE lmode = 0)
 AND vh.id1 = vw.id1
 AND vh.id2 = vw.id2
 AND vh.request = 0
 AND vw.lmode = 0
 AND vh.sid = vs.sid
 AND vw.sid = vsw.sid;




 SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL;

 

==========Find Long SQLsselect v$session_longops.time_remaining, v$session_longops.opname, v$session_longops.start_time,
v$session_longops.last_update_time, v$session_longops.elapsed_seconds, ((v$session_longops.elapsed_seconds /
(v$session_longops.elapsed_seconds + v$session_longops.time_remaining))*100) pct_complete,
V$SQL.SQL_TEXT from v$session_longops, V$SQL where v$session_longops.SQL_ADDRESS=V$SQL.ADDRESS
and v$session_longops.time_remaining <> 0
order by V$SQL.SQL_TEXT, v$session_longops.last_update_time;


==========Kill
alter system kill session '929,23760' IMMEDIATE;
 
==========find pid
select     s.username
,     s.sid
,     s.serial#
,     p.spid
,     last_call_et
,     status
from      V$SESSION s
,     V$PROCESS p
where     s.PADDR = p.ADDR
and     s.sid= 929




==========another way to find sql

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
       SID,   
       MACHINE, 
       REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT, 
      ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT 
  FROM V$SESSION SES,   
       V$SQLtext_with_newlines SQL 
 where SES.STATUS = 'ACTIVE'
   and SES.USERNAME is not null
   and SES.SQL_ADDRESS    = SQL.ADDRESS 
   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE 
   and Ses.AUDSID <> userenv('SESSIONID') 
 order by runt desc, 1,sql.piece;