CODEWITHSUNDEEP
oracle-databasetoad
Kelly
Kelly

Reputation: 1005

Is there a way to connect to a specific instance of Oracle server?

I am not sure if I am using the right terminology, but I noticed while logged into OEM that my Oracle database server is set up with three instances and it seems to randomly pick one when I log in. Is there a way to update tnsnames to connect to a specific instance or do it in the New Connection window in Toad? I'm hoping I can kill a particular session that way.

Upvotes: 0

Views: 1825

Answers (2)

Kelly
Kelly

Reputation: 1005

Answering my own question- in the TNSNames file, you can add an INSTANCE_NAME part like this. I did not figure out how to do it without a TNSNames file.

database =
 (DESCRIPTION =
 (SDU=32767)
   (ADDRESS = (PROTOCOL = TCP)(HOST = server.name)(PORT = 1521)(SEND_BUF_SIZE = 98304)(RECV_BUF_SIZE = 98304))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
       (INSTANCE_NAME = instance_name)
      (SERVICE_NAME = service_name)
    )
 )

As @ik_zelf posted, you can query for which instance you're on using this:

select sys_context('USERENV','INSTANCE_NAME') from dual;

Upvotes: 1

user123664
user123664

Reputation:

Normally, yes you can. If you know on which server the instance is located, you could specify that host with the oracle service name that is running there. Using select instance_name, host_name, logins, status from v$instance; you can find out on which instance on which machine your current session is located.

You can find the other instances by querying gv$instance.

If you have no privileges on [g]v$instance you might want to try select sys_context('USERENV','INSTANCE_NAME') from dual; but this only works for the current instance.

A lot depends on the configuration that is running.

Upvotes: 1

Related Questions