Would someone please give me the sql to query the total size of a database please?
Printable View
Would someone please give me the sql to query the total size of a database please?
Hi
Try this it show in Giga bytes
select sum(bytes)/(1024*1024*1024) from V$datafile
Regards
hi,
select sum(bytes)/(1024*1024*1024) from V$datafile
will give you total available physical storage space
select sum(bytes/1024/1024) from dba_segments
will give you total physical storage space used.
Santosh, Sureshe - a big thank you to both of you!!!
Hi
select sum (bytes/1024/1024/1024) from v$datafile;
will give you the size of your database
Hope this help
Regards
Richard
SELECT TO_CHAR(SUM((a.logfile_size+b.tempfile_size+c.datafile_size)/1024/1024/1024), '999999990.00')"Database Size in GB"
FROM
(SELECT SUM(bytes) logfile_size FROM v$log) a,
(SELECT NVL(SUM(BYTES),0) tempfile_size FROM v$tempfile) b,
(SELECT SUM(BYTES) datafile_size FROM v$datafile) c;
all these SQL just show you the assigned space for your database, for example you have 10GB in datafiles but actually you probably only have 500MB data, so what is the database size are you looking for? The assigned or the real data?
Hi Suresh,
This script will tell you something about the filling of your database
Tycho
select a.tablespace_name,
a.ts_size_in_MB,
b.ts_free_space,
round(((a.ts_size_in_MB-b.ts_free_space)/a.ts_size_in_MB)* 100,2) percentage
from (select tablespace_name,
round(sum(bytes)/(1024*1024)) ts_size_in_MB
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
round(sum(bytes)/(1024*1024)) ts_free_space
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
order by b.ts_free_space