How to know the size of oracle database 11g

This post will helps you to find out the total,free and used size of  oracle database.

To know the total size of your database.

Script:

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

Output:

Size in GB
----------

1115.77302

To know the Total, Used and Free space of your Database.

Script:

col "Database Size" format a20 

col "Free space" format a20 

col "Used space" format a20 

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" 

, round(sum(used.bytes) / 1024 / 1024 / 1024 ) - 

round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" 

, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" 

from (select bytes 

from v$datafile 

union all 

select bytes 

from v$tempfile 

union all 

select bytes 

from v$log) used 

, (select sum(bytes) as p 

from dba_free_space) free 

group by free.p 

/

Output:

Database Size        Used space           Free space

-------------------- -------------------- --------------------

1116 GB              580 GB               536 GB

Below script to know the particular datafile size of your database.

Script:

set pagesize 100

column file_name format a32

column tablespace_name format a15

column status format a3 trunc

column t format 999,999.000 heading "Total MB"

column a format a4 heading "Aext"

column p format 990.00 heading "% Free"

SELECT df.file_name,

df.tablespace_name,

df. status,

(df.bytes/1024000) t,

(fs.s/df.bytes*100) p,

decode (ae.y,1,'YES','NO') a

FROM dba_data_files df,

(SELECT file_id,SUM(bytes) s

FROM dba_free_space

GROUP BY file_id) fs,

(SELECT file#, 1 y

FROM sys.filext$

GROUP BY file#) ae

WHERE df.file_id = fs.file_id

AND ae.file#(+) = df.file_id

ORDER BY df.tablespace_name, df.file_id;

column file_name clear

column tablespace_name clear

column status clear

column t clear

column a clear

column p clear

ttitle off

Output:

FILE_NAME                        TABLESPACE_NAME STA     Total MB  % Free Aext

-------------------------------- --------------- --- ------------ ------- ----

/d05/oracle/proddata/absl01.dbf  ABSL            AVA    1,142.000    6.52 YES

/d05/oracle/proddata/absl02.dbf  ABSL            AVA    1,806.336    6.94 YES

Hope this post will helps you :)