Database size and tablespace Utilization?

In this article, we are going to check full database size, used size, and free space in the database as well as check the tablespace utilization details.

Read: Find redo log members, redo log file size and redo log status

Subscribe on YouTube

Step 1. To check Database total space.

SQL> select sum(bytes/1024/1024/1024) "Database Size(GB)" from dba_data_files;

Step 2. Check Used space in DATABASE.

SQL> select sum(bytes/1024/1024/1024) "Database Used Space(GB)" from dba_segments;

Step 3. To check Free space in DATABASE.

SQL> select sum(bytes/1024/1024/1024) "Database Free space(GB)" from dba_free_space;

Step 4. To check all tablespaces UTILIZATION.

sql> select t.tablespace,  t.totalspace as " Totalspace(MB)",
     round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
     nvl(fs.freespace,0) as "Freespace(MB)",
     round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
     round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
     (select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
     from dba_data_files d
     group by d.tablespace_name) t,
     (select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
     from dba_free_space f
     group by f.tablespace_name) fs
     where t.tablespace=fs.tablespace (+)
     order by "% Free";

Leave a Comment