Session waits on all datababase

You can display information on all database session waits by below query.

SET LINESIZE 200
SET PAGESIZE 1000

COLUMN username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15

SELECT s.username,
       s.sid,
       s.serial#,
       sw.event,
       sw.wait_class,
       sw.wait_time,
       sw.seconds_in_wait,
       sw.state
FROM   v$session_wait sw,
       v$session s
WHERE  s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;

You can run below query to get more information about any session which is determined by previous query.

select * from v$sesion where sid=<session_id>;