Re: Finding the DB State

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/03/14
Message-ID: <350b0aa5.51140002@www.sigov.si>#1/1

On Wed, 11 Mar 1998 10:46:37 +0530, krish <tkrishna_at_baan.com> wrote:

> Is there any v$ table/view that tells me the state of the
>database - whether opened / closed / mounted ??

Instance started, database not mounted:

The vaste majority of V$ views will return "ORA-01507: database not mounted" when you isue a query against them. There are a few, hovewer, that can be querried against them (V$CONTROLFILE, V$PARAMETER, V$ACCESS, V$ VERSION, V$SGA, V$BGPROCESS, V$COMPATIBILITY,...). Some of them will return 0 rows. The easiest way to determine if the database is not mounted is the following query:

SELECT COUNT(1) FROM v$controlfile;

If it returns 0 then the database is not mounted, if it returns > 0 then the database is allready mounted (or even opened).

2. Instance started, database mounted but not opened:

None of the queries against V$ views should return ORA-01507. The odd exception is V$ROLLNAME, which returns "ORA-01219: database not open: queries allowed on fixed tables\views only" - as it is not one of the fixed views !? I noticed this on 7.3.2, don't know about other versions.

However, to determine if database is mounted but not opened (and you know the instance is started), you can use V$ROLLSTAT, which contains no rows until database is opened. So, if the query:

SELECT COUNT(1) FROM v$rollstat;

returns 0 then database is only mounted, if it returns > 0 then database is allready opened.

Regards,

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Sat Mar 14 1998 - 00:00:00 CST