Andrew Fraser DBA

Logon Trigger to Capture Session NLS_Territory

You can see your own sessions nls settings

select * from nls_session_parameters ;

But for other users’ sessions, that information is stored in their own UGA, not accessible outside their session.

So if you need to know what their nls settings are, a logon trigger is needed to record that. Like this:

conn / as sysdba
drop table af_nls
/
create table af_nls (
  af_sid number ,
  af_program varchar2(48) ,
  af_nls_territory varchar2(40) )
tablespace users
/
create or replace trigger sys.logon_af_nls
after logon on database
when ( user != 'SYS' )
declare
  v_sid number ;
  v_program varchar2(48) ;
  v_nls_territory varchar2(40) ;
begin
  select m.sid, s.program into v_sid, v_program
    from v$session s , v$mystat m
    where m.sid = s.sid and rownum < 2 ;
  select value into v_nls_territory from nls_session_parameters
    where parameter = 'NLS_TERRITORY' ;
  insert into af_nls values
    ( v_sid , v_program , v_nls_territory  ) ;
  commit ;
end logon_af_nls ;
/
sho err

Handy if you’ve got this problem.