计算剩余空间大小与使用率:
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)", ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)",BLOCKS USED_BLOCKS
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)", ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)",BLOCKS USED_BLOCKS
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
解决方案 »
- 请各位高手帮忙,谢谢. 连网后提示ORA-12514.
- 超难sql语句,有人会写吗!
- 请高手指点select count(*)的问题
- 取某个字段值的部分字串用什么函数?
- 百分:关于plsql 语言中触发器的声明部分中变量生命周期的讨论
- 郁闷,从SQL SERVER存储过程转到ORACLE来,没写一个都有问题,帮忙看看
- ORACLE9在redhat 9.0下安装问题,各位大虾,求教求教!!在线等待
- Oracle初学者请问两个很简单的问题,可是我不会,很容易得分的哦!!!
- oracle数据库中要是想建立一个存储过程,使其返回一个记录集,应该怎么写啊?into到哪里啊?
- 用Delphi写数据保存功能时报错,提示缺少逗号
- 急问:在Linux下安装Oracle9i出现以下错误!
- 请问如何计算oracle表容量?
Fixed Size
- Contains general information about the state of the database and the instance, which the background processes need to access.
- No user data is stored here.
- This area is usually less than 100k in size. Variable Size
- This section is influenced by the following init.ora parameters
shared_pool_size
large_pool_size
java_pool_sizeIf you are interested in more detailed info about SGA. Do select * fromv$sgastat. It shows all the details about heaps and other interesting things. If you do
select sum(bytes) from v$sgastat it will give you the same number as select sum(bytes) from v$sgaThe bottomline is that you can rely on the show sga command for getting the exact memory allocated to Oracle SGA.However, there is more than that.
Each user process (if you are not running MTS) will take at least 1MB. So, basically if you have 100 users it's already additional 100MB. In addition there are a few parameters in init.ora that can potentially increase the size of the PGA. sort_area_size is the parameter used for sorting forr each session. When the session is done sorting the memory used for sorting is returned to the OS unless there is a sort_area_retained_size greater than 0. In this case memory usage for the session is reduced to the number specified for sort_area_retained_size. When the process is terminated (normally or abnormally) memory (including retained size) is returned to the OS. The are a few more parameters like for cursors, but they are less important.Conclusion:
To calculate the size:
Total Size = SGA + PGA
where SGA is fixed and PGA depends on # of Oracle processes and type of activity for each process.
请各位高手帮忙!!!!!!!!!!!!!!!!