Handling multiple user logins access to a common set of tables is as common in almost any Oracle applications. Typically there are four options to resolve the issue:
Having a private set quickly turns into a maintenance nightmare for large numbers of users; having public synonyms typically means more recursive work for the database to do when accessing objects and also problems when two object names clash in the same database; hard coding the application schema can mean users having to remember this when producing ad-hoc queries; similary the 'alter session' command can only be performed in application code.
Not any more ! Thanks to 8i, a much simpler solution now exists. The new LOGON trigger can be used to issue the 'alter session' command as soon as the user connects. Your trigger code merely needs:
declare c integer; begin c := sys.dbms_sql.open_cursor; sys.dbms_sql.parse(c, 'alter session set current_schema = ...', sys.dbms_sql.native); sys.dbms_sql.close_cursor(c); end;