Re: Question: How to handle duplicate table names?

From: Ari Kaplan <akaplan_at_interaccess.com>
Date: 1996/06/06
Message-ID: <4p7jni$6l4_at_thymaster.interaccess.com>#1/1


Todd Bealor <tbealor_at_secret.mda.ca> writes:

>Hi,
 

>I would appreciate hearing from anyone who has any insight into
>handling the use of duplicate table names. In the production
>version of the applictation this will not be a problem as the
>tables will be accessed via SQL*net thus having different remote
>database extensions.

Oracle does not allow two tables to have the same name and the same owner. There are ways to get around it for what you want to do.

You should make SYNONYMS for the owner.table that you want to reference. Then, when you move the the production server, you do not need to change your code. The actual base tables would always be the same, but you can change the synonym easily to point to whichever owner or table you like.

For more information on synonyms, check the Oracle documentation if you have it.

Good luck.
-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> Visit my Web Page: http://homepage.interaccess.com/~akaplan   <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->




>The problem is that we are building a prototype using one database.
>While it is possible to place the tables into separate table spaces,
>I have not come across any mechanism for specifying which table space
>to look for a table. The only differentiation mechanism that I can
>find in Oracle is the use of the table owner as a prefix to the table
>name.
 

>i.e. GROUP1.STATUS_CODES
> GROUP2.STATUS_CODES
 
>With this approach we would have to transform all of the table names
>in the application programs when we port them to the actual production
>server. i.e. GROUP1.STATUS_CODES --> STATUS_CODES_at_SITE1 etc...
 

>Any alternative solutions would be greatly appreciated.
 

>Thanks,
 

>Todd
 

>------------------------------------------------------------------
>Wm. Todd Bealor LINC Project
>Systems Developer E-mail: tbealor_at_mda.ca
>MacDonald Dettwiler Phone: +1 (613) 723-7667 x28
>240 - 1101 Prince of Wales Dr. Fax: +1 (613) 723-7832
>Ottawa, Ontario, Canada K2C 3W7 URL: http://www.mda.ca
>
>------------------------------------------------------------------
Received on Thu Jun 06 1996 - 00:00:00 CEST

Original text of this message