OracleBlog

Here's an easy one a colleague asked me.

You are logged into your default session (example: sqlplus scott/tiger with no @instance)

You want to know which instance you are logged into.

You can't query V$ tables directly, because you're not logged in as SYS.

So while looking around at similar questions on Jonathan Lewis' cooperative FAQ:
http://www.jlcomp.demon.co.uk/faq/MySessID.html

I saw SYS_CONTEXT. That looked like it would do the trick. So I looked it up in my favourite reference, Dan Morgan's:
http://www.psoug.org/reference/sys_context.html

And came up with this:

SELECT SYS_CONTEXT(‘USERENV’,’DB_NAME’) FROM DUAL;

You can also get the 'INSTANCE' using this, but that doesn't do you any good if you can't query the V$INSTANCE table.

That solves the problem, but I'm sure that's just one of many ways to do it:

1. Some other queries on some system tables, and/or
2. Some command-line command, and/or
3. Some environment file somewhere.

Also, I know some people who put this directly into their sqlplus prompt.