Just coming back to this one. I agree with Yong. In short finding the
connect string is, I think, not possible. It is, however, possible to track
connecting machines, service names in use (and usernames) via auditing,
listener log files etc. This can help you build a list of people/machines
to update. Centralized naming obviously helps considerably here.
On Fri, Jun 9, 2017 at 9:51 PM, Yong Huang <dmarc-noreply@xxxxxxxxxxxxx>
wrote:
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
