View session, process, sql and explain plan

View running statements

select ses.sid, ses.username, ses.terminal, sq.sql_id, sq.sql_text, p.pid, p.spid
from v$session ses
join v$sql sq
on ses.sql_id = sq.sql_id
join v$process p
on ses.paddr=p.addr;

View open cursors for one session

select SID,SQL_ID,SQL_TEXT from v$open_cursor where sid=244;

View explain plan for one sql_id

SELECT * FROM TABLE(dbms_xplan.display_cursor('azbbfqxzp56sw'));