Re: sys_context to get connection string?
Date: Fri, 9 Jun 2017 20:51:07 +0000
Message-ID: <1138250448.7649119.1497041460338_at_mail.yahoo.com>
Andrew,
Yong Huang
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.
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jun 09 2017 - 22:51:07 CEST