Re: sys_context to get connection string?

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Fri, 9 Jun 2017 17:29:47 -0500
Message-ID: <CAJvnOJZccReq5UoaUZjeLh7yHwgvoZpcMLhe9qjiwysJrkBArg_at_mail.gmail.com>



Thanks for the information. I hope to get to a centrally managed tnsnames... Eventually.

On Fri, Jun 9, 2017 at 3:51 PM, Yong Huang <yong321_at_yahoo.com> 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
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Jun 10 2017 - 00:29:47 CEST

Original text of this message