Sessions to Oracle Database

List:

View current database sessions

    SELECT t.SID, t.SERIAL#, t.osuser as "User", t.MACHINE as "PC", t.PROGRAM as "Program"
    FROM v$session t
    --WHERE (NLS_LOWER(t.PROGRAM) = 'cash.exe') -- view sessions from cash.exe program
    --WHERE status='ACTIVE' and osuser!='SYSTEM' -- view user sessions
    --WHERE username = 'schema' -- view sessions for a schema (user)
    ORDER BY 4 ASC;


Find the blocking session

SELECT status, SECONDS_IN_WAIT, BLOCKING_SESSION, SEQ#
FROM v$session
WHERE username=upper('scott');


Kill a session

ALTER SYSTEM KILL SESSION 'SID,Serial#' IMMEDIATE;

Replace ‘SID’ and ‘Serial#’ with the current session values.


Kill all sessions for a specific schema

define USERNAME = "USER_NAME"

begin
    for i in (select SID, SERIAL# from V$SESSION where USERNAME = upper('&&USERNAME')) loop
    execute immediate 'alter system kill session '''||i.SID||','||i.SERIAL#||''' immediate';
    end loop;
end;
/


Track which query the application is executing

SELECT PROCESS pid, sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text
FROM v$session sess, v$sql sql
WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER';