Re: instance_name as unprivileged user

DA Morgan wrote:
>
> Just being picky here but Oracle has deprecated USERENV and recommends
> using, instead, SYS_CONTEXT. The query would be either:
>
> SELECT sys_context('USERENV', 'INSTANCE') FROM dual;
> or
> SELECT sys_context('USERENV', 'INSTANCE_NAME') FROM dual;
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)

Just to make sure OP understands. Before Oracle 10gR2, I don't think you can get instance name (as the value under instance_name of v$instance) unless you're granted select on v_$instance (or select_catalog_role or other similar privs/roles). For a non-RAC database, instance name probably happens to be the same as database name. If so, sys_context('USERENV', 'DB_NAME') serves the purpose. sys_context('USERENV', 'INSTANCE') gives the instance number only. 'USERENV' namespace doesn't have 'INSTANCE_NAME' parameter until 10gR2.

Yong Huang Received on Wed Dec 21 2005 - 13:55:13 CST