Oracle connection string for other user's tables

We have 2 servers with the same database schema, however due to the way our infrastructure is setup on one server we must prefix all table names with a username

ex:

select * from engmon.cmts

However on the other server the query would need to look like this

select * from cmts

This is because one server connects to a table in a different schema/username. Is it possible to change somethi开发者_运维知识库ng in the jdbc url to make both queries look like #2? That way we do not need to dynamically change the sql depending on which server we are connecting to (dev or prod).

Any ideas?

I don't believe you can do anything to the JDBC URL. However, you could issue the SQL statement

ALTER SESSION SET current_schema = ENGMON

immediately after connecting (you could also put this in a logon trigger in Oracle). This would have the effect of instructing Oracle to resolve unqualified object names using the ENGMON schema rather than the current user's schema. This has no impact on permissions-- your session still only has the privileges assigned to the current user, not to the ENGMON user.

Alternately, you could create synonyms for the various tables that you need to access. Either local synonyms (visible just to you) or public synonyms (visible to all users). So

CREATE [PUBLIC] SYNONYM cmts
   FOR engmon.cmts

Synonyms would only need to be created once and would apply to all sessions no matter what application they come from. Setting the CURRENT_SCHEMA would need to be done every time.