Here’s a little SQL that may be helpful in certain troubleshooting situations. It can answer questions like:
- Which ORACLE_HOME is my database running from?
- Which server-side sqlnet.ora is used by my connection (TNS_ADMIN)?
Or more generally speaking: what’s the value of a given environment variable for my session’s server process.
1 2 3 4 5 6 7 8 9 10 11 | with function get_env(var in varchar2) return varchar2 is val varchar2(32767); begin dbms_system.get_env(get_env.var, get_env.val); return get_env.val; end;select get_env('&var_name.') env_val from dual/ |
Examples:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL> rEnter value for var_name: ORACLE_HOMEENV_VAL --------------------------------/u01/app/oracle/product/ora12201 SQL> rEnter value for var_name: TNS_ADMINENV_VAL --------------------------------/u01/app/oracle/network/adminSQL> rEnter value for var_name: PATHENV_VAL -------------------------------- |
Note, Oracle apparently intentionally redacts the value for certain environment variables, e.g. PATH.
But for the use cases above I’ve found it extremely useful…