I know this topic is fairly old, but came upon it as we are having similar issue. Our issue is that we have 6 different plants running 6 different databases with identical database architecture. The difference is the data that resides in the database at each plant. We also use OS authentication for database logons and some users may have access to more than 1 database. I was tasked with coming up with a plan to consolidate the 6 databases into a single database.

The database and software we use were developed by a software vendor which uses synonyms for each user to access the schema. There are synonyms created for every object that exists for the schema owner (developed by software vendor). This caused additional maintenance for us, as we had to run a script to create synonyms each time a new user is created. Additionally, this way of accessing objects would prevent us from consolidating the 6 databases because of the "duplicate" synonym names that would be required to move each plant's objects into its own schema.

After researching for the past couple days (it took me awhile coming from SQL Server and still learning Oracle), I am thinking of dropping all the synonyms that are used to access the software vendor's objects and making use of a logon trigger to alter a user's session to another schema (permissions do not change). For example:

IF ... EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=PLANT1';

IF ... EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=PLANT2';

etc...

Here are some addtl articles:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:588045400346317188

http://www.oracle-base.com/articles/misc/SchemaOwnersAndApplicationUsers.php