cancel
Showing results for 
Search instead for 
Did you mean: 

How to readout the complete database size with SQL ?

Former Member
0 Kudos

Hi all,

how can I readout the complete database size with SQL?

Please give me a soon answer.

Best regards,

Petra

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Try this

Sql:Vinod>select DF.TOTAL/1048576 "DataFile Size Mb",

LOG.TOTAL/1048576 "Redo Log Size Mb",

CONTROL.TOTAL/1048576 "Control File Size Mb",

(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,

(select sum(a.bytes) TOTAL from dba_data_files a) DF,

(select sum(b.bytes) TOTAL from v$log b) LOG,

(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

Which Calculate total space for all datafiles within database which is Near DB size

Vinod

Answers (2)

Answers (2)

Former Member
0 Kudos

Try this...you may need to format the col sizes.

select

df.tablespace_name "Tablespace",

block_size "Block Size",

(df.totalspace - fs.freespace) "Used MB",

fs.freespace "Free MB",

df.totalspace "Total MB",

round(100 * (fs.freespace / df.totalspace)) "Pct. Free"

from

dba_tablespaces ts,

(select tablespace_name,

round(sum(bytes) / 1048576) TotalSpace

from dba_data_files

group by tablespace_name) df,

(select tablespace_name,

round(sum(bytes) / 1048576) FreeSpace

from dba_free_space

group by tablespace_name) fs

where

ts.tablespace_name = fs.tablespace_name

and

df.tablespace_name = fs.tablespace_name(+)

;

vince_laurent
Active Participant
0 Kudos

Look for the cool FREE tool called orastat.

http://dbamon.com/orastat/

I am not the author - just a very satisfied customer.