View user roles in Oracle

To view available system privileges, query DBA_SYS_PRIV.

SQL> select * from DBA_SYS_PRIVS;

To view available roles, query DBA_ROLES.

SQL> select * from DBA_ROLES;

To view roles assignated to a user, query DBA_ROLE_PRIVS.

SQL> select * from DBA_ROLE_PRIVS where grantee = 'BEN';

View SQL text for a specific OS PID

To view SQL text for a specific operatig system PID, first you must obtain the sql_id of that process and then select sqlarea to get the sql text.

$ ps -ef | grep 22910

oracle 22910     1 14 09:16:59 ?       32:09 oracleDBNAME
(DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))

In SQLPLUS run the query:

SELECT s.saddr, s.sid, s.serial#, s.username,
 s.osuser, s.machine, s.program, s.logon_time, s.status, 
 p.program, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.spid IN (22910);

Then run the query:


select sql_id, sql_fulltext, sql_text from v$sqlarea
where sql_id in (' PREV_SQL_ID');


To view OS PID for a specific SID

SELECT p.program, p.spid, s.saddr, s.sid, s.serial#, s.username,
 s.osuser, s.machine, s.program, s.logon_time, s.status 
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid IN (39, 24)