在 Oracle SQL developer 中查找数据库大小
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2287072/
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
find database size in Oracle SQL developer
提问by Stan
In phpmyadmin, it's able to see database disk usage. I was wondering if there's such thing in Oracle SQL developer. Thanks!
在 phpmyadmin 中，可以查看数据库磁盘使用情况。我想知道 Oracle SQL 开发人员中是否有这样的东西。谢谢！
回答by Jeffrey Kemp
select nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKNOWN'))
tablespace_name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0)
size_alloc_bytes,
round(((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*200) used_chart,
to_char(((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100,
'999G999G999G999G999G999G990D00') ||'%' used,
data_files
from ( select sum(bytes)/1024/1024 Kbytes_free,
max(bytes)/1024/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024/1024 Kbytes_alloc,
tablespace_name, count(*) data_files
from sys.dba_data_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
回答by Gary Myers
I'd recommend the Insider extension for SQL Developer (Raptor).
回答by grokster
select sum(bytes) Bytes,
round(sum(bytes)/power(1000,1)) KiloBytes,
round(sum(bytes)/power(1000,2)) MegaBytes,
round(sum(bytes)/power(1000,3)) GigaBytes,
round(sum(bytes)/power(1000,4)) TeraBytes,
round(sum(bytes)/power(1000,5)) PetaBytes,
round(sum(bytes)/power(1000,6)) ExaBytes,
round(sum(bytes)/power(1000,7)) ZettaBytes,
round(sum(bytes)/power(1000,8)) YottaBytes
from dba_data_files;
Ensure that you are logged in with sysdba privileges to run this script.
确保您使用 sysdba 权限登录以运行此脚本。
回答by Vadzim
An oracle database consists of data files, redo log files, control files, temporary files.
The size of the database actually means the total size of all these files.
oracle 数据库由数据文件、重做日志文件、控制文件、临时文件组成。
数据库的大小实际上意味着所有这些文件的总大小。
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual
Source: http://nimishgarg.blogspot.com/2010/05/oracle-total-size-of-database.html
来源：http: //nimishgarg.blogspot.com/2010/05/oracle-total-size-of-database.html
回答by dba.in.ua
If DB is monitored in Grid Control, then, in emrep database execute this query (History of DB size):
如果在 Grid Control 中监视 DB，则在 emrep 数据库中执行此查询（DB 大小的历史记录）：
SELECT DECODE(m.metric_column, 'ALLOCATED_GB', 'ALLOCATED_GB', 'USED_GB', 'USED_GB') AS bb,
m.rollup_timestamp AS rollup_timestamp,
SUM(m.average) AS value
FROM mgmt$metric_daily m,
mgmt$target_type t
WHERE t.target_guid=
(SELECT target_guid FROM mgmt$target WHERE target_name='ORCL' /* Your DB name /
)
AND (t.target_type ='rac_database'
OR (t.target_type ='oracle_database'
AND t.TYPE_QUALIFIER3 != 'RACINST'))
AND m.target_guid =t.target_guid
AND m.metric_guid =t.metric_guid
AND t.metric_name ='DATABASE_SIZE'
AND (t.metric_column ='ALLOCATED_GB'
OR t.metric_column ='USED_GB')
AND m.rollup_timestamp >= '01.01.2010' / Start date */
AND m.rollup_timestamp <= SYSDATE
AND DECODE(m.metric_column, 'ALLOCATED_GB', 'ALLOCATED_GB', 'USED_GB', 'USED_GB')='USED_GB'
GROUP BY DECODE(m.metric_column,'ALLOCATED_GB','ALLOCATED_GB','USED_GB','USED_GB'),
m.rollup_timestamp
ORDER BY 2;
