Re: How to identify the 'current' instance

j4xprel_at_pbsrv10.isp.pacbell.com wrote:
>
> running under hp-ux, after exporting ORACLE_SID=TST, TWO_TASK=DEV and
> then logging on to sql*plus with 'sqlplus user_id_at_T:host_name:PRD' is
> there any way to query the correct current instance?
>
> thanks in advance.

I see that most people are telling you how to get the database name. I would guess that in most cases that is the same as your instance name. However, they can be different, particularly if you are running the Parallel Server option.

The instance name can be gotten from v$thread.

If you are not running parallel server, there will be only one record in v$thread, so you can use

        select instance from v$thread;

If you are running parallel server, there will be one record in v$thread for each instance. You can join v$thread with v$parameter to find out the instance name for your instance.

	select instance
	       from v$thread t, v$parameter p
 	       where t.thread# = to_number(p.value)
               and p.name = 'thread';

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com