The following will discuss the information that can be gathered regarding a specific session. .

The main location for all session information is the v$session view.
The sessions.sql script displays session information using this view along with the v$process, v$sesstat, and v$statname views.
The v$process view provides information about the background processes that service the sessions,
while the v$sesstat and v$statname provide statistical information about the session.

select a.username, a.osuser, a.sid, a.SERIAL# , d.spid,
a.lockwait, a.status, trunc(b.value/1024) as pga_kb, trunc(e.value/1024) as uga_kb,
a.module, a.machine, a.program, a.logon_Time
from v$session a, v$sesstat b, v$statname c, v$process d, v$sesstat e, v$statname f
where a.paddr = d.addr
and a.sid = b.sid
and b.statistic# = c.statistic#
and c.name = 'session pga memory'
and a.sid = e.sid
and e.STATISTIC# = f.STATISTIC#
and f.name = 'session uga memory'
order by 1, 2;