In phpmyadmin, it's able to see database disk usage. I was wondering if there's such thing in Oracle SQL developer. Thanks!
Add a comment
|
5 Answers
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
1 Comment
obesechicken13
I'm getting that "sys.dba_data_files" does not exist.
I'd recommend the Insider extension for SQL Developer (Raptor).
3 Comments
Stan
I like this, but when ever trying to 'open insider view' the sqldeveloper hangs. how to resolve this? Thanks.
Gary Myers
I had problems with some EA editions, but not with the latest SQL Developer version. I also have full privileges on a 10.2.0.4 database.
Stan
Maybe it was due to not have full privilege to database. But is there any error to be trace? Thanks.
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.
Comments
If DB is monitored in Grid Control, then, in emrep database execute this query (History of DB size):
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;
Comments
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.
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
