Re: instance name

From: Fred Stojentin <stojentin_at_orgdv.din.de>
Date: 2000/07/06
Message-ID: <39646200.13501608@news.cis.dfn.de>#1/1

On Wed, 5 Jul 2000 11:53:31 -0700, "SP" <spant1_at_aol.com> wrote:

>How do you find out what instance Oracle is currently running?
>SP
>
>

Using Oracle 7.x you can get the instance name with

connect internal
create or replace view where_am_i as
select
  d.name database,
  rtrim(translate(t.instance,chr(0),' ')) instance from
  v$database d, v$thread t, v$parameter p where
  p.name='instance_number' and
  to_number(decode(p.value,'0'.'1',p.value))=t.thread# ;
grant select on where_am_i to public;
create public synonym where_am_i for sys.where_am_i;

which works fine with and without multiple instances per database. ___
Fred Stojentin Received on Thu Jul 06 2000 - 00:00:00 CDT