Home » SQL & PL/SQL » SQL & PL/SQL » instance names
instance names [message #198670] Wed, 18 October 2006 03:50 Go to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
I have installed 12 instances on the 10g db server.all are

up and running.

I want to get the list of all instances which are up and running in my server.

How can i write code for that?

I want to list all the instance names through frontend;









Re: instance names [message #198715 is a reply to message #198670] Wed, 18 October 2006 05:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> I want to list all the instance names through frontend;
What front-end are you talking about?
Unless you have a database link to all other instances from one instance, it is not directly possible.
Or you have to manually get the information (ps -ef | grep smon), save it in a table/file and view it in your "front-end"
Re: instance names [message #198887 is a reply to message #198670] Thu, 19 October 2006 00:25 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
How is it possible with databaselinks?

Suppose we have a db link to all instances,then?how
Re: instance names [message #198890 is a reply to message #198887] Thu, 19 October 2006 00:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
select * from dba_db_links

But that will only show you the databases to which DB links have been created. It is quite possible that all of the databases are not linked - even if they are on the same server.

As Mahesh said, (if the server is Unix), you can find all of the live databases with
ps -ef | grep smon
but only from the server.

Ross Leishman
Re: instance names [message #198896 is a reply to message #198670] Thu, 19 October 2006 01:03 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
how can i use this in sql?
Re: instance names [message #198919 is a reply to message #198896] Thu, 19 October 2006 03:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How can you use the Unix "ps" command in SQL? You can't. You need Operating System access.

If you really, really want to poll the other databases from within the database, you can get your DBA to set up a stored Java or C procedure that performs an OS call. I believe source code can be found for this on asktom.oracle.com. Do a search.

Ross Leishman
Re: instance names [message #199023 is a reply to message #198919] Thu, 19 October 2006 12:27 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Everyone assumes that when you say you have 12 instances you also mean 12 databases. It is possible - but not common to have a single database serviced by multiple instances. I don't have a multi-instance-single-database test system, but assume that v$instance would list all the instances for that database.

It wouldn't be a clean solution - but if you had Oracle management server running (which was set up to monitor your instances) - you could query the repository for the status.
Previous Topic: Query with group by
Next Topic: CTL file options
Goto Forum:
  


Current Time: Wed Dec 07 10:55:34 CST 2016

Total time taken to generate the page: 0.12256 seconds