How to check 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 checking the tablespace utilization details.

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

Subscribe on YouTube

Query 1. To check Database total space.

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

Query 2. Check Used space in DATABASE.

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

Query 3. To check Free space in DATABASE.

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

Query 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"
     from
     (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

%d bloggers like this:
Enable Notifications    OK No thanks