How to create a logon trigger

In this particular example, I create a logon trigger that sets one of many available NLS session parameters, as well as sets an undocumentet parameter that I was recommended to set by Oracle Support services:

CREATE OR REPLACE TRIGGER logon_optimizer 
after logon on database
begin 
   if user in ('SCOTT','JACK','BOB')
   then 
      execute immediate 'alter session set NLS_LANGUAGE="GERMAN"';
      execute immediate 'alter session set "_optimizer_unnest_disjunctive_subq"= FALSE';
   end if; 
end;
/

Test to verify that it Works:

Connect scott/tiger
set lines 200
col parameter format a30
col value format a40
select * from nls_session_parameters;


PARAMETER                      VALUE
------------------------------ -----------
NLS_LANGUAGE                   GERMAN
NLS_TERRITORY                  NORWAY

Now generate an error:

select * from xxx
              *
ERROR at line 1:
ORA-00942: Tabelle oder View nicht vorhanden