Finding oracle Database locks

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;

--Generate a list of current oracle sids/pids

SELECT   NVL (a.username, 'Null') username, b.pid, a.sid,
         DECODE (a.terminal, '?', 'Detached', a.terminal) terminal, b.program,
         b.spid, a.osuser, a.serial#
    FROM v$session a, v$process b
   WHERE a.paddr = b.addr
ORDER BY A.username, b.pid;

--Displays information on all database session events

SELECT NVL(s.username, '(oracle)') AS username,
       s.sid, s.serial#, se.event, se.total_waits, se.total_timeouts,
       se.time_waited, se.average_wait, se.max_wait, se.time_waited_micro
FROM   v$session_event se, v$session s
WHERE  s.sid = se.sid
 AND    s.sid = &1
ORDER BY se.time_waited DESC;

--Displays I/O information on all database sessions

SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser, s.sid, s.serial#, si.block_gets,  si.consistent_gets,
       si.physical_reads, si.block_changes, si.consistent_changes
FROM   v$session s, v$sess_io si
WHERE  s.sid = si.sid
ORDER BY s.username, s.osuser;

--History of the event in a specific session

select event, total_waits, time_waited*10 tw_ms,
       average_wait*10 aw_ms, max_wait*10 mw_ms
from v$session_event
where sid = 32;

--List all session statistics for particualr sid

select name, value
from v$sesstat s, v$statname n
where sid = 32
and n.statistic# = s.statistic#
order by value desc;

--Check the CPU consumption for a session

select s.value
from v$sesstat s, v$statname n
where s.sid = 32
and n.statistic# = s.statistic#
and n.name = 'CPU used by this session';

--Getting specific row Information

Select owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,
    dbms_rowid.rowid_create (
        1, o.data_object_id, row_wait_file#,
        row_wait_block#, row_wait_row#
    ) row_id
from v$session s, dba_objects o
where sid = &sid
and o.data_object_id = s.row_wait_obj#;