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
DBA Tips Data Pump Reference


Post a Comment


dba topics. Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google