Re: Rewriting sql queries depending on the source ip address

Chris Lever <af_inet_at_web.de> schreef in berichtnieuws 1cea756d.0307112315.4d078efa_at_posting.google.com...
| Hello,
|
| I have 10 client machines which connect to 10 seperate Oracle servers.
| The name of the database on each server is db1. Now these Oracle
| servers should be consolidated and only one server for all databases
| should be used. But the database names in the clients are hardcoded.
| So i need a way to rewrite these database names dependending on the
| source ip address of the sql-query.
|
| client1.company.com wants to use db1 -> connect to db1 on new server
| client2.company.com wants to use db1 -> connect to db2 on new server
| client3.company.com wants to use db1 -> connect to db3 on new server
|
| Does anybody know a tool which can do this?
|
| Thanks for your interest,
| Chris

I assume the setup is currently like this: client1 connects to db1 on server1 and must now connect to db1 on servernew client2 connects to db1 on server2 and must now connect to db2 on servernew etc.
My answer would be: they use a sqlnet configuration, don't they? With a tnsnames.ora?
For client2 I would say to change the tnsnames.ora entry. Keep the name of the alias the same but change the destination from db1 on server2 to db2 on servernew.
But then I get confused when you say you need to rewrite the sql-query. Why? You want the query to go to db2 on servernew? But the sqlnet configuration is in betweeen, dealing with it? So why rewrite the sql?

I see something in the idea of Sybrand who proposes to make 1 database on the new server instead of a database for each client. Specially when the databases are relatively small, there is a lot of overhead. Each database needs system, temp, rollback tablespaces, an SGA, background processes. But I need more details of the setup to see if it is technically possible. There are more reasons to decide if one database is an option, like what happens in case of a restore. Then all clients are affected. Received on Sat Jul 12 2003 - 04:47:16 CDT