A query to get the ORACLE_HOME path in an Oracle Database.
GOAL

I need to know from which ORACLE_HOME a database instance is running.

SOLUTION

DBMS_SYSTEM

-- Querying ORACLE_HOME with PL/SQL

COL ORACLE_HOME FOR A30
VARIABLE ORACLE_HOME VARCHAR2(100);
EXEC DBMS_SYSTEM.GET_ENV('ORACLE_HOME',:ORACLE_HOME);
PRINT ORACLE_HOME
ORACLE_HOME
------------------------------
/u01/app/oracle/product/19c

SYS_CONTEXT

-- Only applies to oracle 12c and higher

COL ORACLE_HOME FOR A30
SELECT SYS_CONTEXT ('USERENV','ORACLE_HOME') ORACLE_HOME FROM DUAL;
ORACLE_HOME
------------------------------
/u01/app/oracle/product/19c

SYS.V_$PARAMETER

-- Only applies if a spfile is being used

COL ORACLE_HOME FOR A30
SELECT DECODE(VALUE, NULL, 
       'UNABLE TO IDENTIFY ORACLE_HOME',
        SUBSTR(VALUE, 1, INSTR(VALUE, '/', -1, 2) -1)
        ) ORACLE_HOME
FROM 
       SYS.V_$PARAMETER WHERE NAME='spfile';
ORACLE_HOME
------------------------------
/u01/app/oracle/product/19c

DBA_LIBRARIES

-- Only applies to version 12.2.0.1 and lower
-- For details see Doc ID 2145739.1

COL "ORACLE_HOME" FOR A30
SELECT NVL(SUBSTR(FILE_SPEC, 1, INSTR(FILE_SPEC, '\', -1, 2) -1), 
       SUBSTR(FILE_SPEC, 1, INSTR(FILE_SPEC, '/', -1, 2) -1)) "ORACLE_HOME"
FROM DBA_LIBRARIES WHERE  LIBRARY_NAME = 'DBMS_SUMADV_LIB';
ORACLE_HOME
------------------------------
/oracle/product/12.2.0.1
REFERENCES

ORDIMLIBS and DBMS_SUMADV_LIB libraries use hard-coded Oracle Home path (Doc ID 2145739.1)

Written At
07 NOV 202112:10
OEL Release
7.9 x64
Database Release
19.12.0.0.0