I created the below trigger in the same manner in the SYS schema on two different Oracle 11g servers, without any errors.
CREATE OR REPLACE TRIGGER LOGON_TRG
AFTER LOGON ON DATABASE
if (user in ('mytestro', 'mytestrw')) then
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = mytest';
then null; -- prevent a login failure due to an exception
On one server I can tell it is working since I can select any table in mytest as soon as I logon with either account. On the other server it says the table does not exist (ORA-00942). If I issue
ALTER SESSION SET CURRENT_SCHEMA = mytest; manually I can access the table, so it seems the trigger is not firing.