It is common in our organization for GIS staff, and occasionally other GIS users, to have 2 versions of Oracle client on their machines: Oracle 9i and Oracle 11g. The need for 2 Oracle clients is driven by non-GIS reasons; however, depending on which SDE instance people connect to, the direct connect strings vary. For example, the two most common direct connects are:
SDE:ORACLE9i:LEGACYINSTANCE
SDE:ORACLE11G:INSTANCE
Since the LEGACYINSTANCE has SDE 9.2 SP6, Oracle 11g client cannot be used to connect from ArcGIS Desktop 9.3/9.3.1.
It could very well be that both connections are using the Oracle 11g client since it is the first Oracle client in the system's path. I guess that is a question for Esri. If that is the case, i.e., both use the same Oracle client, then my original question should have been:
How does one specify different Oracle direct connect libraries in the Database Connection?