views:

2282

answers:

2

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.

+6  A: 

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;
Tony Andrews
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
Couldn't you create a mapping table that maps User A to Schema B and then query that table in the trigger? So eliminate the WHEN (USER = 'A'), look up the user in this mapping table, and then decide whether you need to change the current schema
Justin Cave
Actually I'm using now the AFTER logon ON DB WHEN (1=1) part, and it works, thanks for the help Tony.
Zsolt Botykai
A: 

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.

Ryan Cook