Re: Application & Table Merging

From: Mark Rosenbaum <mjr_at_netcom.com>
Date: 1997/03/05
Message-ID: <mjrE6LAo4.DM8@netcom.com>#1/1

In article <331C60DB.3B51_at_it.glasgow.gov.uk>, Alan Moore <alan.moore_at_it.glasgow.gov.uk> wrote:
>Hi all,
>
>Could anyone possibly suggest a solution to this problem.....
>
>My site currently has an Application residing as 4 separate
>Instances/UNIX boxes - representing 4 geographical areas. The database
>schemas are identical in every case.
>
>A requirement has now arisen to merge the 4 Instances into one (i.e.
>merge data from 4 table occurences into 1 occurence), on a single UNIX
>platform.
>
>The data merge is relatively straightforward - BUT !...
>
>A User updating their subset (view) of data also requires 'read only'
>access to the other 3 representative areas IN THE SAME TABLE.
>
>The intention here is to attempt a DBA solution as opposed to changing
>the entire Application code.
>

Alan,

If I understand the problem correctly you want to combine/merge 4 seperate tables running on 4 seperate instances into a single table running on a single instance. Furthermore you want 3 out of the four tables to be select only and the fourth to be select/update.

DISCLAIMER: I have not tried this and probably will not but here is an approaches to try. This will be very release dependent. BTW please post the outcome.

If you need all four tables to be access under the same name then try

Creating the four different tables in one instance Create a view with all 4 tables (see partitioned views in the tunning guide) Create a second view using the first view (as of 7.2 you could only update

	 a view if it was on a single table. Sometimes you can fool Oracle
	 into thinking that it is a single table with a view on a view)

Create a role that has three of the tables with select and one as select/update Test to see if the permissions on the underlining table will override the

        views(ie try to update into one of the select tables) If this works then create 3 more roles each with a different table read/write with the rest as readonly.

This is only an idea on how it MIGHT be done I have not tested it.

Good Luck

mjr Received on Wed Mar 05 1997 - 00:00:00 CST