Query to Find Out The Total File Size And Data Occupied Size Of Oracle Database

1. Here is the query to find out the total file size of a Oracle database

select b.name,

( select sum(bytes)/1024/1024/1024 DF_size from dba_data_files ) +

( select nvl(sum(bytes),0)/1024/1024/1024 TF_size from dba_temp_files ) +

( select sum(bytes)/1024/1024/1024 RLF_size from sys.v_$log ) +

( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 CF_size from v$controlfile) "Size in GB"

from  dual,  (select name from v$database ) b

SQL> /

NAME      Size in GB

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

PRODTAB   63421.3552

2. Here is the query to find out the data occupied size for an Oracle database

SQL> SELECT name,sum(bytes)/1024/1024/1024 AS "Size in GB" FROM dba_segments,

(select name from v$database) group by name;

NAME      Size in GB

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

PRODTAB   25640.0562