Use of PROXY USER...

Product version:

Oracle 10g Release 2: 10.2.0.2.0
Solaris 10

Hi All,

We have a "Hot" dispute on how one can use Oracle Server PROXY user to connect to different schemas. Let me describe the problem first.

PROBLEM: We need to load large quantity of data to our server from time to time and put it to use on our production site. So far we create new schema and load the data while the site is running on old data. After the load we do index creation+Analyze etc and make data ready to be used. When time comes, we switch the user via synonyms - drop the synonyms to the old data in old schema and create new synonyms pointing to new tables in the new schema.

Now some developer trembled upon the Oracle PROXY user documentation and asks the following.

PROXY SETUP: Create old and new schemas. Create a proxy user. Let Application connection using proxy user. Let "proxy user connect to" old schema. When time comes, make "proxy user to connect to" new schema. So Application connection still remains the same. We - DBAs - do change the proxy user to connect to new schema.

1> Does anybody has done setup like "PROXY USER" above? 2> In my understanding, I think it's not possible. PROXY USER can be used but application must change connections to connect to new schema. Am I correct in that?
3> Ultimately I am suggesting following. We create old and new schemas. We also create a common (though not proxy) user and we create roles for both old and new schemas. Now when times come, I modify the common user, take away old role and provide a new role granting access to new schema. Is this the best possible way?

-Thanks for advise.

DP

--

http://www.freelists.org/webpage/oracle-l Received on Mon Nov 06 2006 - 09:50:27 CST