There is no simple way to achieve this. You can either do it by tracing the session or by using a login trigger. Here's an example of how to do it using a DB login trigger.
Logging table:
create table nls_session_parameters_log
(
inserted_date date,
sid number,
username varchar2(128),
parameter varchar2(30),
value varchar2(40)
);
Public grant and synonym:
grant insert on nls_session_parameters_log to public;
create public synonym nls_session_parameters_log for sys.nls_session_parameters_log;
Database login trigger:
create or replace trigger nls_log_dbtrig after logon on database
begin
if user in ('PHIL') then
insert into nls_session_parameters_log
select sysdate, (select sys_context('USERENV','SID') from dual), (select user from dual),parameter, value
from nls_session_parameters;
end if;
exception
when others then
null;
end;
/
Test:
SQL> conn phil/phil
Connected.
SQL> select count(*) from nls_session_parameters_log;
COUNT(*)
----------
17
SQL>
Obviously you'd be better off putting the table in an appropriate schema and granting on a per-user basis.
If you need help with another approach (session tracing), let me know.
I know this question is more than a year old, but in case anyone stumbles here like I did, you can query V$SES_OPTIMIZER_ENV:
SYS> select name, value
from V$SES_OPTIMIZER_ENV
where sid=54
and name='hash_area_size';
NAME VALUE
—————————————- ————————-
hash_area_size 100000
Source: http://oracleinaction.com/other-session-params/
Best Answer
SESSION_ROLES view shows list of roles assigned after you makes session in instance as those roles granted to your connected users. you can view other users granted roles using below:
If you want to know list of roles of particular user, use below query:
Please note that you must have DBA privilege to view above. and you can't see list of roles using currently connected others session. you can only see what role the can be use after getting connected.