Find database size in Oracle SQL developer

Question

In phpmyadmin, he can see database disk usage. I was wondering if there is such a thing in the Oracle SQL developer. Thanks!

Answer 1

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 

A source

Answer 2

I would recommend the SQL Developer Extension (Raptor) .

Answer 3

If DB is controlled in Grid Control, then in the emrep database, run 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; 

Answer 4

 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; 

Make sure you are logged in with sysdba privileges to run this script.

Answer 5

The oracle database consists of data files, redo log files, control files, temporary files.

Database size 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