How to check database size in Oracle

A DBA works on many aspects of database like cloning, backup, performance tuning etc. In every aspect of database administration, most of the times resolution depends upon the size of database. For example, DBA can implement DB FULL backup strategy on a very small database when compared to DB INCREMENTAL strategy on a very large database.

Use below script to check db size along with Used space and free space in 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 /