Can anyone help me out with an PL/SQL query to get the ORACLE_HOME path in Oracle 11G?
I require it to be fetched from the Oracle DB only & not via the Operating system's environment variables.
PS: I'm a DB newbie.
1
-
2Why do you need this?miracle173– miracle1732015-04-09 09:26:49 +00:00Commented Apr 9, 2015 at 9:26
Add a comment
|
2 Answers
There is sys_context function
that works for Oracle 12c
select SYS_CONTEXT ('USERENV','ORACLE_HOME') from dual;
This does not work for lower versions of Oracle. For 11g and lower versions, I don't know a simple method.
PS: I read @Balasz' answer and now I know a simple answer for version 11g.
-
The link to
sys_contextis not right. Please correct it.Kaushik Nayak– Kaushik Nayak2018-04-13 03:09:32 +00:00Commented Apr 13, 2018 at 3:09 -
@KaushikNayakk thnxmiracle173– miracle1732018-04-13 07:36:28 +00:00Commented Apr 13, 2018 at 7:36
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> var oracle_home clob;
SQL> exec dbms_system.get_env('ORACLE_HOME', :oracle_home);
PL/SQL procedure successfully completed.
SQL> print oracle_home
ORACLE_HOME
--------------------------------------------------------------------------------
/oracle/base/product/db11204ee
But you want it in a query. This later version also allows you to grant execute just on this single function to users with lower privileges, instead of the whole DBMS_SYSTEM package:
create or replace function get_oracle_home return clob
as
oh clob;
begin
dbms_system.get_env('ORACLE_HOME', oh);
return oh;
end;
/
SQL> select get_oracle_home from dual;
GET_ORACLE_HOME
--------------------------------------------------------------------------------
/oracle/base/product/db11204ee
-
if you will give "ps -ef |grep tns" then get all details about your Oracle_HomeMd Haidar Ali Khan– Md Haidar Ali Khan2015-04-09 13:55:33 +00:00Commented Apr 9, 2015 at 13:55
-
2@Md Haidar Ali Khan: Even if the listener runs from the same Oracle Home, you can do this only on the database server itself.Balazs Papp– Balazs Papp2015-04-09 14:08:45 +00:00Commented Apr 9, 2015 at 14:08