How to check Database size and tablespace Utilization?

Checking the database size and tablespace utilization is an important task for database administrators to monitor and manage the growth of their databases. The size of a database can affect the performance and availability of the system, and monitoring tablespace utilization helps ensure that there is enough space for data to be stored.

Database size and tablespace Utilization?

To check the size of a database and its tablespace utilization, you can use various SQL commands and tools, depending on the database management system you are using. For example, in Oracle, you can use the SQL command "SELECT SUM(BYTES)/1024/1024/1024 GB FROM DBA_SEGMENTS" to get the size of the database in GB.

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

Subscribe on YouTube

Step 1. To check Database total space.

Using the following SQL command you can get the total size of datafiles.

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

Step 2. Check Used space in DATABASE.

The below command will show you the total used size of the 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.

To check the utilization of all tablespaces in a database, you can use SQL commands or tools specific to your database management system. The process of checking tablespace utilization involves determining the amount of space that is currently being used, as well as the amount of space that is available for future data storage.

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";