From:Steve Adams
Date:05-May-2001 15:44
Subject:   Avoiding public synonyms

Yes, the ALTER SESSION SET CURRENT_SCHEMA command is the obvious choice here. If you have access to the application source code, it is preferable to code it into each application so that that the same user will be able to use more than just one application. If you choose to use a trigger, consider making it an ON DATABASE trigger and using a table to map users to application schemas as follows ...

        SQL> create table system.default_schema (user_name varchar2(30), schema_name varchar2(30));

        Table created.

        SQL> insert into system.default_schema values ('SCOTT', 'SYSTEM');

        1 row created.

        SQL> create or replace trigger system.set_current_schema after logon on database
          2  declare
          3    default_schema  varchar2(30);
          4  begin
          5    select schema_name into default_schema from system.default_schema where user_name = user;
          6    execute immediate 'alter session set current_schema = ' || default_schema;
          7  end;
          8  /

        Trigger created.

        SQL> connect scott/tiger
        SQL> select username, schemaname from v$session where audsid = userenv('sessionid');

        USERNAME                       SCHEMANAME
        ------------------------------ ------------------------------
        SCOTT                          SYSTEM

The single ON DATABASE trigger will place less load on the library cache than a set of equivalent triggers on individual user schemas. Of course if you go with this approach, the DEFAULT_SCHEMA table should be stored in a single table hash cluster.

On the question of public synonyms versus private synonyms, the extract of the paper that Melissa posted on MetaLink is wrong to state that private synonyms are worse than public. Private synonyms do preclude the need for negative dependency tracking and do not require distinct versions of the cursor for each user as that paper suggests. Although synonyms are regarded as objects in the data dictionary, they are never referents in the library cache. Instead, synonym translation structures are maintained in the library cache. I explained a little about synonym translations in a recent answer on Synonyms. Anyway, the answer to this question is that public synonyms are much worse, although it should be remembered that this is a scalability issue, and not a performance issue in most cases.

I noticed your name when I was looking at 3 threads on MetaLink.

Doc Id   5297.997   Poor Performance using public synonyms
Doc Id 180536.999   Performance of Public Synonyms vs Private Synonyms
Doc Id  79400.999   Q: default session set current_schema=:user_id
I am porting several Oracle 7 applications into one 8i database. These applications are used to having a database to themselves and I was hoping to eliminate public grants/synonyms for security reasons, naming clashes etc.

Would the following approach work? No synonyms at all, but no hardcoded schema names either. I have no idea how the approaches works internally or what the performance will be like. I tried it and it seems to work for the situation I am interested in (many users accessing 1 schema at any one time).

        CREATE OR REPLACE TRIGGER stuart.CTSS_event_logon
        ON stuart.SCHEMA
                  EXECUTE IMMEDIATE 'alter session set current_schema = ctdba' ;
        WHEN OTHERS THEN raise_application_error(-20001, 'Problem: ' || SQLERRM );
        END CTSS_event_logon;