Oracle setting per user default scheme (not altering a session)

Question

Hi all,

is there a way to change an oracle user's default schema?

I found it in the FAQ that I can alter it in the session, but it's not what I want. E.G. the user at log on always sees another schema as default.

Thanks in advance.

Answer 1

I believe a logon trigger should work:

CREATE OR REPLACE TRIGGER db_logon
AFTER logon ON DATABASE WHEN (USER = 'A')
BEGIN
    execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = B';
END;

Answer 2

For some reason Tony's trigger did not work for me. However, a slightly different trigger that I found on the web using the same concept did.

create or replace trigger set_default_schema
after logon on my_user.schema
begin
  execute immediate 'alter session set current_schema=NEW_SCHEMA';
end;

I just wanted to throw it out there in case someone else has the same issue.