我在学习oracle内存结构问题时,遇到了下面的问题:问题1:
select count(*) from dba_objects;提示出错:database not open:queries allowed on fixed tables/views only问题2:在计算命中率时:
select a.value+b.value "logical_reads",c.value "phys_reads",100*((a.value+b.value)-c.value)/(a.value+b.value) "buffer hit ratio" from v$sysstat a,v$sysstat b,v$sysstat c where a.statistic#=40 and b.statistic#=41 and c.statistic#=42;提示出错:divisor is equal to zero!!!
想了好久,都没解决,等待高手!!!
select count(*) from dba_objects;提示出错:database not open:queries allowed on fixed tables/views only问题2:在计算命中率时:
select a.value+b.value "logical_reads",c.value "phys_reads",100*((a.value+b.value)-c.value)/(a.value+b.value) "buffer hit ratio" from v$sysstat a,v$sysstat b,v$sysstat c where a.statistic#=40 and b.statistic#=41 and c.statistic#=42;提示出错:divisor is equal to zero!!!
想了好久,都没解决,等待高手!!!
解决方案 »
- db_block_checking和db_block_checksum区别
- QQ的数据库关系图
- 一个月份统计,求解决办法。高手来。
- 大家好,sqlserver转oracle,大家帮忙这怎么改啊
- 求问一个物化视图的刷新问题
- 十万火急!!!occi 操作数据表问题!!
- 需要建表,但是可能已经有了这个表了,该怎么办呢?
- ORA-06553错误什么意思?
- sql*plus忘记了密码怎么能在进去啊?
- 求一个SQL语句,谢谢
- 请问PHP连接OR后,如何让执行这个命令是正确的返回1||||oracle连接PHP中和mysql_num_rows函数相同的是那个,
- 求条简单的SQL语句,当场验证,立马给分!谢谢
提示出错:database not open:queries allowed on fixed tables/views only 数据库没有open,可能只是mount状态。 动态表和性能图要数据库open状态才能访问。
2.
提示出错:divisor is equal to zero!!!
除数是0,不能被除要查一下data才知道具体问题。
SQL> select count(*) from dba_objects; COUNT(*)
----------
50932SQL> select a.value+b.value "logical_reads",c.value "phys_reads",100*((a.value+b.value)-c.value)/(a.value+b.value) "buff
er hit ratio" from v$sysstat a,v$sysstat b,v$sysstat c where a.statistic#=40 and
2 b.statistic#=41 and c.statistic#=42;logical_reads phys_reads buffer hit ratio
------------- ---------- ----------------
5072 60710912 -1196881.7
把数据库重启下看看...
SQL> select count(*) from dba_objects; COUNT(*)
----------
49799SQL> select a.value+b.value "logical_reads",c.value "phys_reads",100*((a.value+b
.value)-c.value)/(a.value+b.value) "buffer hit ratio" from v$sysstat a,v$sysstat
b,v$sysstat c where a.statistic#=40 and b.statistic#=41 and c.statistic#=42;logical_reads phys_reads buffer hit ratio
------------- ---------- ----------------
10832 370688000 -3422056.6SQL>
他这里的问题都是由于数据库没有open导致的。
数据库open的话,就没有这个问题了,所以你们的机器上都是Ok的
第一個問題是由於database not open,但第二個不是吧SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.Total System Global Area 1157627904 bytes
Fixed Size 2083432 bytes
Variable Size 436209048 bytes
Database Buffers 704643072 bytes
Redo Buffers 14692352 bytes
Database mounted.
SQL> select count(*) from dba_objects;
select count(*) from dba_objects
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views onlySQL> select a.value+b.value "logical_reads",c.value "phys_reads",100*((a.value+b.value)-c.value)/(a.value+b.value) "buff
er hit ratio" from v$sysstat a,v$sysstat b,v$sysstat c where a.statistic#=40 and b.statistic#=41 and c.statistic#=42;logical_reads phys_reads buffer hit ratio
------------- ---------- ----------------
115 1090560 -948213.04SQL> alter database open;Database altered.SQL> select a.value+b.value "logical_reads",c.value "phys_reads",100*((a.value+b.value)-c.value)/(a.value+b.value) "buff
er hit ratio" from v$sysstat a,v$sysstat b,v$sysstat c where a.statistic#=40 and b.statistic#=41 and c.statistic#=42;logical_reads phys_reads buffer hit ratio
------------- ---------- ----------------
261 2898944 -1110606.5
出现了如下:
error at line 1:
ora-01157: cannot identify/lock data file 11-see dbwr trace file
ora-01110:data file 11:'E:\DATAFILE\O1.DBF'但是在E盘中没有这个路径:'E:\DATAFILE\01.DBF'
SQL> select count(*) from dba_objects;
select count(*) from dba_objects
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views onlySQL> select a.value+b.value "logical_reads",c.value "phys_reads",100*((a.value+b.value)-c.value)/(a.value+b.value) "buff
er hit ratio" from v$sysstat a,v$sysstat b,v$sysstat c where a.statistic#=40 and b.statistic#=41 and c.statistic#=42;logical_reads phys_reads buffer hit ratio
------------- ---------- ----------------
115 1090560 -948213.04SQL> alter database open;Database altered.
当我按照你的思路做下去,却怎么也没结果,即
select a.value+b.value "logical_reads",c.value "phys_reads",100*((a.value+b.value)-c.value)/(a.value+b.value) "buff
er hit ratio" from v$sysstat a,v$sysstat b,v$sysstat c where a.statistic#=40 and b.statistic#=41 and c.statistic#=42依然显示:divisor is equal to zero!!!