Useful Oracle Scripts

Count running queries by username.sql

SELECT
    sql_id,
    COUNT(*)
FROM
    (
        SELECT
            t.sql_id,
            s.sid,
            COUNT(*)
        FROM
            v$sqltext_with_newlines t,
            v$session s
        WHERE
            t.address = s.sql_address
            AND t.hash_value = s.sql_hash_value
            AND s.username = 'username'
        GROUP BY
            t.sql_id,
            s.sid
        ORDER BY
            t.sql_id,
            s.sid
    ) aa
GROUP BY
    sql_id;

Find blocking sessions.sql

SELECT
    (
        SELECT
            username
            || ' - '
            || osuser
        FROM
            v$session
        WHERE
            sid = a.sid
    ) blocker,
    a.sid
    || ', '
    || (
        SELECT
            serial#
        FROM
            v$session
        WHERE
            sid = a.sid
    ) sid_serial,
    ' is blocking ',
    (
        SELECT
            username
            || ' - '
            || osuser
        FROM
            v$session
        WHERE
            sid = b.sid
    ) blockee,
    b.sid
    || ', '
    || (
        SELECT
            serial#
        FROM
            v$session
        WHERE
            sid = b.sid
    ) sid_serial
FROM
    v$lock a,
    v$lock b
WHERE
    a.block = 1
    AND b.request > 0
    AND a.id1 = b.id1
    AND a.id2 = b.id2;

Find running queries by username.sql

SELECT
    s.username,
    s.sid,
    s.serial#,
    s.osuser,
    t.sql_id,
    sql_text,
    status
FROM
    v$sqltext_with_newlines t,
    v$session s
WHERE
    t.address = s.sql_address
    AND t.hash_value = s.sql_hash_value
    AND s.username = 'username'
ORDER BY
    username,
    s.sid,
    t.piece;

Kill session.sql

--ALTER SYSTEM KILL SESSION  'sid,serial#';
ALTER SYSTEM KILL SESSION '186,7947';