Skip to Main Content
  • Questions
  • Query DBA_ views when database is in mount mode


Question and Answer

Tom Kyte

Thanks for the question, Seyyed Reza.

Asked: June 20, 2012 - 1:11 am UTC

Last updated: August 28, 2013 - 6:59 pm UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

I shutdown the database by runing
command and ran thw following commands. I wanted to know why I can't query the DBA_ views when my database is in mount mode but I can query V$ views(or may be some of them) when in mount mode?

Thank you

ERROR at line 1:
ORA-01507: database not mounted

ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


Database altered.



ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


Database altered.



and Tom said...

v$ views are dynamic performance views, mostly built on top of MEMORY structures, not disk.

dba_ views are based on disk data pretty much, the database has to be not only mounted but opened to access any datafiles.

dba_ views are in datafiles.

most all v$ views, based on x$ tables, are in memory data structures, not disk.


  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment


A reader, June 20, 2012 - 5:10 pm UTC


In which area of the memory structure do the V$ views reside? And how much memory do they comsume?

Thank you very much Tom
Tom Kyte
June 21, 2012 - 8:03 am UTC

SGA mostly.

as for the memory question, it depends on the structure being queried obviously. Different structures take different amounts of memory.

do not worry, they are not "overhead" or anything, they are structures we need to run the database - even if the v$views were not there, they would still be there.

Hayat, June 22, 2012 - 4:36 am UTC


As we know that V$Views are taking data from data dictionary, and it is normally resides in System tablespace,

if at that system tablespace datafile have problem, we have read the data from it


Thanks and Regards

Tom Kyte
June 22, 2012 - 7:19 am UTC

v$ views are taking data from memory structures typically. The data dictionary is considered to be the set of 'real' tables that live on disk.

v$ views are generally accessible even when the datafiles are not - because they do not come from the datafiles in general.

Seyyed Reza, June 22, 2012 - 5:24 pm UTC


Tom you said:
most all v$ views, based on x$ tables, are in memory data structures, not disk.

And I search Google and found the following statment that the x$ tables are not really tables, they are C language structures inside the SGA RAM heap.

Is that right about C language structures inside the SGA RAM heap?


Tom Kyte
June 23, 2012 - 1:48 pm UTC

that is what in memory data structures in a big C program would be - yes.

Oracle is written predominantly in C.

So, when you query v$ tables, you are typically looking at the formatted contents of arrays, linked lists, trees and so on.

Hayata, June 26, 2012 - 5:02 am UTC


It means when we add datafile or any modfication in controlfile, it is also written in Linked List / Array strucutre in file format.

Oracle can read this file, and placed its contents in SGA, although at that DB Structure not linked with Instance.


Tom Kyte
June 26, 2012 - 8:03 am UTC

sure, that is one way to think about it.

I just say "v$ tables are in memory structures". To me, that is more than enough said.

Circular view definition in V$ views

Prajjwal Mallik, August 09, 2012 - 5:25 am UTC

Hi Tom,
After firing the following SQLs, what I saw is a bit wierd at least for me. The public synonym V$BH points to the view SYS.V_$BH. However, the underlying SQL for the view SYS.V_$BH in turn selects from the public synonym V$BH.

SQL> select OWNER, OBJECT_NAME, OBJECT_ID,  OBJECT_type from dba_objects where OBJECT_NAME in ('V_$BH','V$BH');

OWNER                          OBJECT_NAME                     OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------------
SYS                            V_$BH                                1397 VIEW
PUBLIC                         V$BH                                 1398 SYNONYM


OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
PUBLIC                         V$BH                           SYS                            V_$BH

SQL> set long 1000000
SQL> SELECT TEXT from dba_views where VIEW_NAME='V_$BH';


I tried to make up a similar scenerio involving circular view definition.

SQL> sho user
SQL> create table T1 (aa int);

Table created.

SQL> create view V1_ as select * from T1;

View created.

SQL> create public synonym V1 for V1_;

Synonym created.

SQL> insert into T1 select rownum from dual connect by level<=5;

5 rows created.

SQL> commit;

Commit complete.

SQL> select * from V1_;


SQL> create or replace view V1_ as select * from V1;
create or replace view V1_ as select * from V1
ERROR at line 1:
ORA-01731: circular view definition encountered

So my questions are -
1) How did oracle manage to create circular view definition?
2) I guess, v$BH fetches data from X$BH internally (I might be wrong). From where exactly to vaidate this if not from dba_views?


Tom Kyte
August 17, 2012 - 1:22 pm UTC

1) it is by magic, we just know what to do for v$ stuff
2) it is an in memory data structure - x$bh is. most of the v$ views query data structures, not tables. We just have the code to deal with it.

they are magic, special, exceptional - and the code just knows what to do with them.


Prajjwal Mallik, August 09, 2012 - 2:22 pm UTC

Something that I discovered just now.. to partially answer my second question.

SQL> set autot trace exp
SQL> select * from v$bh;

Execution Plan
Plan hash value: 798134341

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |      |     1 |   164 |     1 (100)| 00:00:01 |
|*  1 |  HASH JOIN OUTER  |      |     1 |   164 |     1 (100)| 00:00:01 |
|*  2 |   FIXED TABLE FULL| X$BH |     1 |   134 |     0   (0)| 00:00:01 |
|   3 |   FIXED TABLE FULL| X$LE |   100 |  3000 |     0   (0)| 00:00:01 |

Predicate Information (identified by operation id):

   1 - access("BH"."LE_ADDR"="LE"."LE_ADDR"(+))
   2 - filter("BH"."INST_ID"=USERENV('INSTANCE'))

select * from v$bh; very close to...
select * from x$bh bh, x$le le where BH.LE_ADDR=LE.LE_ADDR(+) and BH.INST_ID=USERENV('INSTANCE');

Grant Privilege on v$views to users

Vimal, December 01, 2012 - 3:40 am UTC

Hi Tom,
I tried to grant privilege on v$database to a user. But it returned the error: ORA-02030: can only select from fixed tables/views.
After Googling I understood that v$database is a synonym of actual views v_$database. So I thought we can't grant privilege on synonyms.
When I experimented this myself by manually creating synonym for a table and then granting privilege on that synonym to a user, it succeeded.
How was it impossible to grant privilege on v$database and similar syninyms? Kindly guide me Tom.


Tom Kyte
December 03, 2012 - 8:16 am UTC

v$ stuff is "magic", as many SYS things are. They are special, they are different. They are sort of burned into the kernel, they are not normal dictionary objects.

the error message hints at this fact:

ops$tkyte%ORA11GR2> !oerr ora 2030
02030, 00000, "can only select from fixed tables/views"
// *Cause:  An attempt is being made to perform an operation other than
//         a retrieval from a fixed table/view.
// *Action:  You may only select rows from fixed tables/views.

these are "fixed tables/views", not real database objects in the sense that you and I understand. They are not tables on disk - they are views on top of in memory data structures.

v$ and v_$

Vimal, December 03, 2012 - 9:18 pm UTC

Tom, I will ask the question precisely! Granting privilege on v_$ (views) is perfectly working. But the same is failing with v$ (synonyms). Can't we grant privileges on Synonyms? I tried it by creating my own synonyms and granting privilege, it is working fine. But why not the same with v$ (synonyms)?
Tom Kyte
December 04, 2012 - 7:39 am UTC

I'll stick with "sys is special, sys is magic, v$ views are even more magical, more mystical, they are not normal views in any sense"

they are views only in name and looks, they are not views as you know them, they are projections on in memory data structures.

cummulative values in V$

Andre, August 01, 2013 - 8:44 am UTC

Hi Tom

I would like to clear up how useful (if at all) is to probe the V$FILESTAT view to check specifically for its two values - as below:

The consultant who had been engaged executes the SQL statement below and says that we need to worry about Oracle I/O should these values go over 150 (i.e. 1.5 sec)

Tom - correct me if I am wrong - but these two particular attributes (MAXIORTM,MAXIOWTM) display the High-Water mark each. So in my opinion even if they show high values for two data-files (like below) - it is impossible to say if this long I/O persisted for a long time - or was a recurring issue - or was just a spike.

The database has been running 24x7 8 days in a row and in the first 4 days these values had only reached 132 - and on the file #32 (which still displays the same).

Unless the database is bounced - this V$FILESTAT view will keep on accumulating data - so I would very much question the usefullness of these two attributes specifically.

But I might be wrong - and the consultant might be correct in asserting that we have a problem.

BTW - this is the same database that had been deployed on one single logical volume that is also mapped to 12 other hosts - so IMHO - such spikes in I/O would be attributed to the fact that storage is SHARED rather than that there is any particular issue with one of the database files.

YOUR OPINION please...?

Kind regards

SQL> select file#,MAXIORTM,MAXIOWTM FROM v$filestat order by MAXIORTM DESC;

---------- ---------- ----------
        18        348         48
        19        175         74
        16        132         74
        14        131         75
        11        126         57
        17        123         53
         2        120         75
        31        113         75
         1        108         58
        15        102         58
        21         99         74
        12         86         57
        20         81         74
        32         79         77
        13         77         57
        30         70         75
         5         41          5
         3         12          5
         7         10          4
         6          9          5
        10          6          4
        22          4          2

Tom Kyte
August 02, 2013 - 7:07 pm UTC

you are correct, it shows a highwater mark.

that said, a read time of 3.48 seconds is pretty darn high, in computer terms - it is glacial. even 1.5 seconds is.

v$event_histogram for db file scattered/sequential read would be more telling - better describing if you have a problem.

A single 3.5 second IO - not good, but not bad (probably - there are cases where it definitely could be horrendous - but you'd already know about it, your database would have hung..)

Lots of them - very bad.

You need a histogram to figure out how many IO's are taking such and such a period of time.

(I'd just say you have a problem if you have a performance relevant database on a SAN shared by 12 other systems........ I wouldn't even need to run a query ;) )


Andre, August 01, 2013 - 4:49 pm UTC


a short PS note to what I said earlier today

I have researched this further and found out about DBMS_SYSTEM.kcfrms packaged procedure

This indeed does reset these values (and more).

In this case HWM are NOT useless as they can be reset by some job which would probe the V$FILESTAT

Would you give any further recommendations as to how one should monitor this...?

Is there any specific HWM value which should cause concern?

Thank you

Tom Kyte
August 02, 2013 - 7:23 pm UTC

use v$event_histogram to see if you have a historical problem.

dbms_system is not exactly documented. You can mess up other things with this reset (other tools/reports).


Andre, August 03, 2013 - 12:30 pm UTC

Hi Tom

Thank you for your response to both of my posts - re MAXIOxxx attributes in the V$FILESTAT.

The consultant keeps executing the query every hour as before only to find these values suddenly had gone through the roof..!!

>> Please see below the output from V$FILESTAT
-- with the max(MAXIORTM) = 133.64 sec and max(MAXIOWTM) = 223.49 sec.

BTW: He received a response from the infra/SAN team:
>>>-q/ There was a DWDM link failure this afternoon (3 SAN circuits on a single fabric - second fabric unaffected). Based on the time of your email I suspect that the degraded response times may coincide with any path failover events. Incident occurred circa 16:00-16:30. Root cause is still under investigation

Re your recommendation:
I would have resorted to histogram views also - but the v$event_histogram had NOT been made available on Oracle 9i - only introduced with 10g.


Your statement "... mess up other things with this reset ..." ==> I interpret to be that various other accrued attributes in other V$ views would also be reset to zeros thus possibly affecting STATSPACK report...? for a while until more data gets collected - or database is restarted?

So - correct me if I am wrong - this would be the only impact - right..?

>>> Well -
It has been going the full circle -
- - i.e. to the root cause being SAN's one single managed disk (striped over 96 DASD) => mapped to one logical volume => this in turn provisioned to 13 hosts and presented as file systems => with anyone of the other databases or other purpose machines going to town on I/O (as per my other posts on ASM deployed on a set of raw devices to replace the current shared SAN RAID-5 infrastructure) ...

I would love to be able - under these very high constraints (no access to any tools) to be able to make a case ONLY to be allowed bench-mark tests between:
(1) SAN "AS IS NOW" - and
(2) RAW devices that are OUTSIDE their "managed-disks" - accessible to ASM - (these raw devices would need to be housed on a SAN + accessible via some switch via disk controllers etc., as the logical volumes are now - but at least these would NOT be shared)

Say that you need to make a case like that - i.e. convince the management to at least allow you to try bench-mark tests - how would you go about it

Please keep in mind -
(a) old (EOL=2009) HP machine
(b) old Win-2000 / no tools
(c) old Oracle 9i

making a case so that at least someone may listen and give a "Go-Ahead" to get bench-mark tests underway.

Thank you

Kindly - Andre

---------- ---------- ----------
1 108 58
2 120 22349
3 12 5
5 41 5
6 9 5
7 10 4
10 6 4
11 126 57
12 86 57
13 77 57
14 131 75
15 102 58
16 132 74
17 123 22349
18 348 48
19 175 22349
20 13364 22349
21 99 22349
22 4 2
30 84 75
31 113 75
32 83 22349

Tom Kyte
August 08, 2013 - 4:17 pm UTC

So - correct me if I am wrong - this would be the only impact - right..?

who knows, you are using an undocumented package to reset counters we are not really expecting, 3rd party tools are not expecting, to be reset.

Say that you need to make a case like that - i.e. convince the management to at
least allow you to try bench-mark tests - how would you go about it,

i would just say "we experience wildly varying IO response times, this affects you and all of your applications. If you would like to start getting consistent, fast response times from your applications for you and your users - we need to evaluate this - get numbers, become numerate. The only way to do that is by benchmarking"

but given that you are working with software/hardware from over a decade in the past - it might not really matter anyway.

benchmarking + Jonathan Lewis use of kcfrms

Andre, August 09, 2013 - 9:19 am UTC

Hi Tom,

Thank you for a very succinct suggested advice to the mgt re benchmarking of I/O on SAN etc...

Re the use of the dbms_system.kcfrms undocumented procedure - I have just spotted Jonathan Lewis article in which he is using it - although he also gives a warning later in the same note - to be careful doing it on systems with large number of users...

Here is the URL




Andre, August 26, 2013 - 8:34 am UTC

Hi Tom,

I am trying to reconcile the times being gathered on two SYS views: FILESTAT and V_$FILE_HISTOGRAM

On a 11g database (that is also sharing SAN) - and that had been deployed without ARCHIVELOG the team shuts it down every weekend to take an old fashion cold backup.

Let us forget NOARCHIVELOG for a moment.

The important thing is that all V$ views get reset on Saturday.

I have just checked these 2 views and I do not understand why the times gathered are so different...?

E.g.: MAXIORTM = 4871 ... MAXIOWTM = 2204

but ... max(SINGLEBLKRDTIM_MILLI) = 1024

48.71 sec on Read or 22.04 sec on Write vs 1 sec recorded in the histogram ..???

The file# with recorded 1sec on histogram is for SYSAUX TBS
this is reflected with MAXIOWTM 22.04 sec - however MAXIORTM for the same file is 0.56 sec.

I tried to research this - but could not find any info other than very basic references in Oracle docs.

How can I trust these measurements...?

Are there some conditions that need to be met for these measurements to be reconciled or at least be closer..?

Thank you - as always

Tom Kyte
August 28, 2013 - 6:59 pm UTC

see for a good write up....

(v$filestat shows multiblock reads too - which v$file_histogram does not - that is all about single block IO's)