I have recently stumbled upon V$SESSION_CONNECT_INFO view and discovered that it provides interesting information about client-side software and settings.

Using this view in Oracle 11g you can simplify collecting some statistics about database clients. Here is what can be extracted:

• Version of client libraries
• Type of OCI library used (standard OCI, different instant clients and etc.) but no JDBC support it seems
• Client characterset (new in 11g – doesn’t work for pre-11.1 clients and for JDBC thin)
• Authentication type (username+password, OS based, proxy and etc.)

How many times have you been in the situation when you need to know certain attributes of your clients to evaluate impact of a coming change of a bug you just hit? Unless the environment is very simplistic or just recently setup, there is usually no easy way to discover every client driver used to connect. This view can workaround absence of standard policies and documentation, procedure violations and you can confidently determine which client versions are where. You can even setup monitoring and pro-actively generate an alert when violations are detected which would be my preferred way.

If you decide to use this feature, you might want to create an AFTER LOGON trigger – sampling of could be not enough to catch short-living sessions.

I haven’t used this view myself and I’m writing this on the plane as I’ve just come across it in the documentation and though that this rather unknown feature would be useful on the blog.

It’d be nice to have that info available in audit views but I couldn’t find it in DBA_AUDIT_SESSION and DBA_AUDIT_TRAIL. DBA_AUDIT_TRAIL has just column COMMENT_TEXT where we can see authentication type for session records.

Oh… which plane am I on? That deserves a separate blog post. Stay tuned!

PS: Oh… I’ve seen a ghost of Dave Ensor now on the plane… It’s probably not such a good idea to read Oracle manuals on the plane - it was just a man with very similar face profile. Yeah, I better switch to a movie then!

PPS: Actually, it took me few days to post this entry. Shame on me but better sooner then later… Oh… I mean better later than never!