0

If I understood it correctly, when the instance is started, some views are loaded into memory and we can query against it like v$instance. And it doesn't need db to be mounted.

If so, are there any other views which can be queried, apart from v$instance?

Also, when I query v$database, it says database is not mounted, but a different error when I try to give a random name. Is it just different error handling for the view name v$database (as it seems to be considered something important or known database) or any other reason for this?

SQL> select * from v$database;
select * from v$database
              *
ERROR at line 1:
ORA-01507: database not mounted


SQL> select * from v$data;
select * from v$data
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


SQL> select * from testing;
select * from testing
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

Update:

It seems the following views also work: v$session, v$sql.

The reason for asking is I just want to understand the scope and purpose of each view or database table practically.

0

Generally speaking, most of the dynamic performance views, known as V$ views, which are populated in the memory, SGA to be specific are accessible after the instance startup process. If the these V$ views contains information which are stored in physical files such as control files or data files then your database has to be mounted and/or opened.

For example, when your database instance is started but database is unmounted, you can describe the v$database view but can't access it's contents until the database is mounted. This is due to the fact that the view has to get information from control files which is inaccessible until the database is mounted.

SQL> startup nomount

Instance is started.

SQL> desc v$database

Can view the structure becuase it is pupulated in SGA.

SQL> select * from v$database;
select * from v$database
              *
ERROR at line 1:
ORA-01507: database not mounted


SQL> alter database mount;

Database altered.

SQL> select * from v$database;

Now, you may access it's contents.

v$instance is accessible after the instance is started. As it's name suggests, it contains information related to memory and processes.

  • Thanks. Is there any condition that some v$ views must be loaded? As when you said only desc works, so for this to work at least the views containing descriptions of all the tables should be loaded first right? I am trying to categorize all the views and when each and every view is loaded/used so that I can remember them all. – GP92 Mar 19 '17 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.