oracle 估计数据库大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5772898/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Estimate the database size
提问by user722902
We have a clustered database with two nodes. My objective is to find out the size of the database. Could you please give me a script to estimate the size of the database?
我们有一个带有两个节点的集群数据库。我的目标是找出数据库的大小。你能给我一个脚本来估计数据库的大小吗？
回答by ik_zelf
A good script is go to the dba, give a few beers and you will get what you want. If that does not help, check the v$datafile, v$tempfile and v$log views. They will give you all needed data, if you have access to them, in which case you probably are the dba.
一个好的脚本是去 dba，给一些啤酒，你会得到你想要的。如果这没有帮助，请检查 v$datafile、v$tempfile 和 v$log 视图。如果您有权访问它们，它们将为您提供所有需要的数据，在这种情况下，您可能是 dba。
select sum(bytes)/1024/1024 MB from
( select sum (bytes) bytes from v$datafile
union
select sum (bytes) from v$tempfile
union
select sum (bytes * members) from v$log
)
/
I hope this helps.
我希望这有帮助。
回答by Ravi
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB"
from ( select sum(bytes)/1024/1024/1024 data_size
from dba_data_files) a,
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size
from v$controlfile) d
回答by taepodong1101
Use the code below to get DB size. Yes its the same as above but you can put it in a nice PL/SQL script to run in different databases.
使用下面的代码获取数据库大小。是的，它和上面的一样，但是你可以把它放在一个很好的 PL/SQL 脚本中以在不同的数据库中运行。
SET SERVEROUTPUT ON
Declare
ddf Number:= 0;
dtf Number:= 0;
log_bytes Number:= 0;
total Number:= 0;
BEGIN
select sum(bytes)/power(1024,3) into ddf from dba_data_files;
select sum(bytes)/power(1024,3) into dtf from dba_temp_files;
select sum(bytes)/power(1024,3) into log_bytes from v$log;
total:= round(ddf+dtf+log_bytes, 3);
dbms_output.put_line('TOTAL DB Size is: '||total||'GB ');
END;
/
http://techxploration.blogspot.com.au/2012/06/script-to-get-oracle-database-size.html
http://techxploration.blogspot.com.au/2012/06/script-to-get-oracle-database-size.html
回答by user2316634
A slight modification to Jaun's query to include members from v$log as was pointed out and this would probably be the most accurate because it includes the controle file info which is part of the overall database size.
正如所指出的那样，对 Jaun 的查询稍作修改以包括来自 v$log 的成员，这可能是最准确的，因为它包括控制文件信息，该信息是整个数据库大小的一部分。
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB"
from ( select sum(bytes)/1024/1024/1024 data_size
from dba_data_files) a,
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes*members)/1024/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size
from v$controlfile) d
