Find ORACLE DB size

 

Use below query to get the size of a database.

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
/

Database Size	     Used space 	  Free space
-------------------- -------------------- --------------------
8 GB		     5 GB		  3 GB 

To Check the database size physical consume on disk.

select sum(bytes)/1024/1024/1024 size_in_gb from dba_data_files; 

SIZE_IN_GB
----------
32.1853638 

 Check the total space used by the data.

select sum(bytes)/1024/1024/1024 size_in_gb from dba_segments;

 SIZE_IN_GB
----------
27.8783569

To Check the size of the User or Schema in Oracle.

select owner, sum(bytes)/1024/1024/1024 Size_GB from dba_segments group  by owner;

OWNER         SIZE_GB
---------- ----------
MDSYS      .080688477
CTXSYS     .003723145
SYSTEM     .002990723
EXFSYS     .003601074
DVSYS      .004455566
AUDSYS     .031738281




To Check the PDB Size of the databases
select con_id, name, open_mode, total_size/1024/1024/1024 "PDB_SIZE_GB" from v$pdbs;

Check the CDB Size of the databases  

select sum(bytes)/1024/1024/1024 from cdb_data_files;