Total DB Tablespace Size

Controlling and monitoring of database tablespace was a crucial job for a database administrator. When your database table runs out of tablespace, the user will faces error when submitting concurrent request that involve CREATE and INSERT statement.

SQL> SELECT SPACE.tablespace_name, SPACE.total_space, free.total_free,
ROUND(free.total_free/SPACE.total_space*100) AS pct_free,
ROUND((SPACE.total_space-free.total_free),2) AS total_used,
ROUND((SPACE.total_space-free.total_free)/SPACE.total_space*100) AS pct_used,
free.max_free, NEXT.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) SPACE,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free,
ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent
FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE SPACE.tablespace_name = free.tablespace_name (+)
AND SPACE.tablespace_name = NEXT.tablespace_name (+)
AND (ROUND(free.total_free/SPACE.total_space*100)<> free.max_free)
ORDER BY pct_used DESC;

This script will return the total tablespace usage information.

SQL> SELECT tablespace_name, bytes/1024/1024 FROM dba_free_space;

This script will return the free space left in the tablespace information

SQL> SELECT file_name, SUM(bytes)/(1024*1024) FROM dba_temp_files
GROUP BY file_name;

SQL> SELECT tablespace_name, SUM(bytes)/(1024*1024) TEMPSIZE FROM dba_temp_files
GROUP BY tablespace_name;

This 2 script will return the usage of TEMP tablespace. The TEMP tablespace will be used if your SQL is running with group by, order by, nested select joining which requires TEMP tablespace for this sorting purposes

SQL> SELECT a.data_size+b.temp_size+c.redo_size "total_size"
FROM ( SELECT SUM(bytes) data_size
FROM dba_data_files ) a,
( SELECT nvl(SUM(bytes),0) temp_size
FROM dba_temp_files ) b,
( SELECT SUM(bytes) redo_size
FROM sys.v_$log ) c;

This script can be used to fetch the total size of data files allocated for database system

SQL> SELECT tbs.tablespace_name,
tot.bytes/(1024*1024) "Total Space in MB",
round(tot.bytes/(1024*1024)- SUM(nvl(fre.bytes,0))/(1024*1024),2) "Used in MB",
round(SUM(nvl(fre.bytes,0))/(1024*1024),2) "Free in MB",
round((1-SUM(nvl(fre.bytes,0))/tot.bytes)*100,2) Pct,
decode(
greatest((1-SUM(nvl(fre.bytes,0))/tot.bytes)*100, 90),
90, '', '*'
) Pct_warn
FROM dba_free_space fre,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) tot,
dba_tablespaces tbs
WHERE tot.tablespace_name = tbs.tablespace_name
AND fre.tablespace_name(+) = tbs.tablespace_name
GROUP BY tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes
ORDER BY 5 DESC, 1;

This scripts gives warning indicator for all tablespaces that have less then 90% free space in them with an asterisk in the last column.

« Number Of Total Completed Concurrent Request

» Database Session Memory Usage

Total DB Tablespace Size

Related Posts

Recent Posts