How to get ORACLE_HOME from data dictionary?
February 12th, 2006 By Vadim Bobrov
On UNIX:
select substr(file_spec, 1, instr(file_spec, '/', -1, 2) -1) from dba_libraries where library_name = 'DBMS_SUMADV_LIB'
On Windows, substitute with backslash.
Vadim Bobrov
Oracle Database Tools
http://www.fourthelephant.com
February 12th, 2006 at 6:50 pm
Note that you may not have access to dba_libraries. You may use user_libraries or all_libraries.
I tested the above query on my Windows XP running Oracle XE (connected as sys as sysdba), here is what I got: C:/ADE/aime_051006/oracle/rdbms/. The funny thing is, I do not seem to find this directory at all on my C drive ?!
February 12th, 2006 at 7:21 pm
correct. I get the same (non-existent) directory. Does not seem to work on XE. On Enterprise 8i, 9i, 10g works fine
Vadim Bobrov
Oracle Database Tools
http://www.fourthelephant.com
July 21st, 2006 at 4:35 am
This is also not the “ORACLE_HOME” that is used to start the instance. To get the ORACLE_HOME used to start the instance, you can do :
SQL> set autopri on SQL> var oracle_home varchar2(255) SQL> exec dbms_system.get_env('ORACLE_HOME',:ORACLE_HOME) PL/SQL procedure successfully completed. ORACLE_HOME -------------------------------------------------------------------------------- /app/oracle/oracle/product/10.2.0/db_2