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

Question

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.

Solution 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;

Solution 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.

Solution 3

create or replace trigger AFTER_LOGON_TSFREL
AFTER LOGON ON "TSFRELEASEAPP".SCHEMA
BEGIN
   EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=TSF_RELEASE';
END;

Comments

[Zsolt Botykai]

Yes it was the only solution I had found, but I don't want to create a really long query (e.g. WHEN USER IN (really really long list). Any another way?

[Zsolt Botykai]

Actually I'm using now the AFTER logon ON DB WHEN (1=1) part, and it works, thanks for the help Tony.

[lexu]

I got it to compile only after I removed the semicolons behind <NEW_SCHEMA'> and the final <end> ... but it still doesn't work, the default schema doesn't change when logging in! I guess I'm missing some privileges on the DB!

[melanke]

I have an user called "melanke" and a schema called "melanke". What I have to do if I want to query select * from tb_melanke without having to query select * from melanke.tb_melanke? This triggers are not working.

[Falco]

The Triggers should work, do some debugging, by trying to execute the ALTER SESSION yourself after logon, if that works make sure your Trigger is set and compiled with the right privileges... Another option would be (public) SYNONYMS