Re: Creating the same tables twice in one database instance

Ben Ryan wrote:
>
> Using Oracle I am trying to setup a set of tables belonging to a
> particular application twice within a single database instance for
> two different groups of people. Both groups of people must use the
> same application SQL code, but with different data.
> (If I were using Sybase I would just create two databases with the
> same tables and then create user accounts with the default database
> set appropriately.)
>
> I do not want the SQL (&PL/SQL) application code to prefix table
> references with a schema name. (i.e. both groups use the same
> application code.)
>
> Within each user group there needs to be one Oracle account that
> creates the tables etc.
> The rest of the users are restricted (via GRANT commands) in the ways
> they can access the tables.
>
> Currently I cannot see how this can be done without having two
> instances of the database (with all the overhead that implies.) Can
> you suggest an alternative.
>
> To respond with direct email please use ben_ryan_at_vancity.com
>
> Ben Ryan - VanCity C.U.
> Vancouver, B.C.
> Canada
Ben,

How about using private synonyms? This would afford you a number of options. The tables could either be in the same schema with different names, different schemas with the same names, or in entirely different database instances.

-- 
Rich Woods
Technical Field Support Specialist, Oracle Corporation, USA
The above statements and opinions are my own and do not
necessarily represent those of Oracle Corporation.