Menu Bar

Thursday, November 15, 2012

Question to self: Who am I ?


"I believe the appropriate metaphor here involves a river of excrement and a Native American water vessel without any means of propulsion." - Dr. Sheldon Cooper, The Big Bang Theory.
Today we'll talk about some Oracle stuff for a change.

I had one of my colleagues come to be a couple of weeks ago and say to me "How to I tell what database I'm connected to after I connect to the database" ?

I had to scratch my head a little on that one. When connected as SYSDBA, it's very simple to select from v$instance and you have your instance name.

However, if you're not connected as SYSDBA how do you do it.

Enter the SYS_CONTEXT function. SYS_CONTEXT allows you to extract parameter values associated with a particular namespace. Oracle provides a predefined namespace called USERENV which contains a wealth of information about the current session.

The following, for example would tell you which database you're currently connected to:


SELECT SYS_CONTEXT ('USERENV', 'INSTANCE_NAME') 
   FROM DUAL;

There's a full list of the predefined parameters in the Oracle SQL Language Reference Manual.