Andrew,
I've done research on this. It is not possible to find the connect string the
user uses from the database side. Since we often have the need to find this
information, and we use OID (Oracle Internet Diretory) to centrally manage
connect strings, we use one little trick to achieve this. We create a new
service in the database and change the service_name of the connect identifier
(OID entry) to use that service. Then we watch for this service name in
listener.log. (I wish dba_audit_trail or sys.aud$ had service but there is
not.) You can also check v$session for service_name but you'll miss the
connections that come and go quickly.
This approach also works if tnsnames.ora is centrally managed as on a shared
file server, or many copies are always sync'ed. This won't work if every user
configures his own tnsnames.ora. In that case, you can email them all and tell
them the expected tnsping output, and notify you only if the output is
different. Alternatively, if most users use the centrally managed tnsnames,
then change the service_name and watch for the original service name in
listener.log for the minority of users.
Yong Huang
