The server is heavily loaded, there are oracle processes causing high cpu usage. You want to know what the session causing the high cpu usage is doing.
This shows all privilegues for a given user, including those inherited from his role(s)
SELECT PRIVILEGE FROM sys.dba_sys_privs WHERE grantee = '&username' UNION SELECT PRIVILEGE FROM dba_role_privs rp JOIN role_sys_privs rsp ON (rp.granted_role = rsp.role) WHERE rp.grantee = '&username' ORDER BY 1;
This lists open/active sessions.
select username , count(status) as Sessions from v$session where username is not null --and status = 'ACTIVE' group by username order by Sessions desc;
By os user
select osuser , count(status) as Sessions from v$session where username is not null --and status = 'ACTIVE' group by osuser order by Sessions desc;
This list users who are queued up due to other session holding a lock on objects.
SELECT substr(s1.username,1,12) "WAITING User", substr(s1.osuser,1,8) "OS User", substr(to_char(w.session_id),1,5) "Sid", P1.spid "PID", substr(s2.username,1,12) "HOLDING User", substr(s2.osuser,1,8) "OS User", substr(to_char(h.session_id),1,5) "Sid", P2.spid "PID" FROM sys.v_$process P1, sys.v_$process P2, sys.v_$session S1, sys.v_$session S2, dba_locks w, dba_locks h WHERE w.mode_held = 'None' AND h.mode_held != 'None' AND h.mode_requested = 'None' AND w.mode_requested != 'None' AND w.lock_type (+) = h.lock_type AND w.lock_id1 (+) = h.lock_id1 AND w.lock_id2 (+) = h.lock_id2 AND w.session_id = S1.sid (+) AND h.session_id = S2.sid (+) AND w.session_id != h.session_id AND S1.paddr = P1.addr (+) AND S2.paddr = P2.addr (+) ;