How can I view what NLS settings are active for other session?
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.