Re: How to find out concrete ORACLE_HOME via SQL?

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 13 Aug 2006 19:51:52 -0700
Message-ID: <1155523913.29872@bubbleator.drizzle.com>

Bob Jones wrote:

> "Mladen Gogala" <gogala_at_sbcglobal.net> wrote in message 
> news:pan.2006.08.13.23.29.04.865393_at_sbcglobal.net...

>> On Sun, 13 Aug 2006 23:13:56 +0000, Bob Jones wrote:
>>
>>> That works only if the spfile at default location is used.
>> Yes, of course. In 99% of the cases you will get the correct result,
>> though. In case of some RAC instances, you'll find spfile on raw devices.
>> Oracle instance doesn't record ORACLE_HOME in any of the virtual tables
>> that I know of. It is likely that this trick will help, but it is not
>> guaranteed.
>>
>> --
>> http://www.mgogala.com
>>

> 
> Actually it is quite common for the spfile to be placed outside of 
> $ORACLE_HOME directory, such as /u01/admin. 

That is my experience as well. I think what you want is:

SELECT name, value
FROM gv$parameter
WHERE name = 'spfile';

But that won't give you the value for $ORACLE_HOME.

The way you get Oracle home is as follows:

set linesize 131
set pagesize 20

col host_name format a30
col home_name format a20
col home_location format a50

SELECT *
FROM sysman.mgmt$software_homes;

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sun Aug 13 2006 - 21:51:52 CDT