I have a logon trigger that seems to be executing when I logon to my database. I'm trying to set the current_schema at that time and when I run:
select sys_context('userenv', 'current_schema') from dual;
I see that my current_schema is the users schema (e.g. USER2) and not the one I'm setting it to (e.g. USER1). This is my trigger:
CREATE OR REPLACE TRIGGER db_logon
AFTER logon ON DATABASE WHEN (USER IN ('USER2'))
execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = USER1';
The user has alter session privileges. Anyone have any helpful hints??