How can I view what NLS settings are active for other session?

Question

I've got another session that I suspect isn't running with the 'proper' NLS settings for us in Oracle 11gR2. What can I query to show the NLS settings for a different active session - all of the NLS_* views reference my current session.

Answer 1

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.

Answer 2

The only (that i know of) NLS type value that is easily retrievable from the data dictionary is client_charset found in V$SESSION_CONNECT_INFO

ie

select * from V$SESSION_CONNECT_INFO;