how to find a session id based on query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: how to find a session id based on query

  1. #1
    Join Date
    Dec 2000
    Posts
    40
    hi friends,

    A user has opened 4 sessions on different client machines. In one of that, he has a long running delete command which needs to be killed now. How to find the session id to kill only that session. Rest of the sessions should be continued to run.

    thanks in advance.

    ms reddy


  2. #2
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hi

    I think this query might just work ...........beware use it at your own risk i havent tested it though.hope thsi is what you are lookin for

    set verify off lines 192 head on

    col UNAM format a20 word heading 'User'
    col STMT format a56 word heading 'Statement'
    col RUNT format a08 word heading 'Run Time'
    col ltim format a20 word heading 'Logon Time'
    col etim format a20 word heading 'Connect Time'
    col PROG format a30 word heading 'Program|Client Terminal Details'
    col SID format a10 word heading 'SID/|Serial#'
    col DR format 999999999 heading 'Disk Reads'
    col BG format 999999999 heading 'Buffer Gets'
    col sqltext format A64 wrap heading 'Last SQL'

    break on unam on sid on status

    select S.USERNAME||'('||s.sid||')-'||s.osuser UNAM
    -- ,s.program||'-'||s.terminal||'('||s.machine||')' PROG
    ,s.sid||'/'||serial# sid
    ,s.status "Status"
    ,sql_text sqltext
    from v$sqltext_with_newlines t,V$SESSION s
    where t.address =s.sql_address
    and t.hash_value = s.sql_hash_value
    order by s.sid,t.piece

    regards
    hrishy

  3. #3
    Join Date
    Mar 2001
    Posts
    188
    hi,

    take this i havn't test it, but i think it helps you

    select
    s.osuser,
    p.spid "PID",
    s.sid "SID",
    s.terminal,
    s.program,
    s.status,
    to_char(logon_time, 'DD-MON HH24:MI:SS') "Login Time"
    from
    v$session s,
    v$process p
    where p.addr = s.paddr
    and s.osuser != 'oracle'
    order by pid;
    Best Regards
    Thomas Schmidt

    Thomas_Schmidt@eplus-online.de
    If you have no aims, you will never reach a goal !!!

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    hi tom

    your query dosent give the last sql that the suer is executing.

    regards
    hrishy

  5. #5
    Join Date
    Mar 2001
    Posts
    188
    hi hrishy
    you have right, but the querry gives you a lot of information and if i combinate your statment with my statment you get so much infos you want.
    But in the future i will read the question better.
    Excuse me but my enlish isn't so well.
    Best Regards
    Thomas Schmidt

    Thomas_Schmidt@eplus-online.de
    If you have no aims, you will never reach a goal !!!

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Minor confusion... Why not just run TopSessions in the diagnostic pack and look at the various connections for that user? Find the one with the active Delete statement in it and kill it.

    - Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width