Referencing objects in a schema

Here's the problem: We are trying to simulate multiple independent "databases" inside one instance of the Oracle Server (v7.1) by storing replications of all tables inside different schemas (e.g. we set up two users, each of whom owns an identical set of objects). The idea is that application users, each with his/her own username and password, can log in to our app and then log in to different "databases" by internally switching all SQL references to point to the objects in a particular schema.

The problem arises when we use views or stored procedures/functions. Since the functions themselves belong to the "database", not to the user, we also must create private synonyms to every one of these objects (there are hundreds). This is very time consuming.

Is there any way to reference all objects belonging to a particular schema in one step? That is, can user Scott log in to Oracle as Scott, and use all of user David's objects, without having to a)establish a private synonym for each object or b)explicitly reference David's schema in each SQL statement? Received on Mon Sep 30 1996 - 00:00:00 CEST