Find Out Tablespace Free Space

A database is divided into logical storage units called tablespaces, which group related data blocks, extents, and segments. For example, tablespaces commonly group together all application objects to simplify some administrative operations.

Following is the script to find out the percentage used of all tablespaces.

SELECT a.tablespace_name,
a.bytes bytes_used,
b.bytes bytes_free,
b.largest,
ROUND(((a.bytes-b.bytes)/a.bytes)*100,2) percent_used
FROM (SELECT tablespace_name,
SUM(bytes) bytes
FROM DBA_DATA_FILES
GROUP BY tablespace_name)a,
(SELECT tablespace_name,
SUM(bytes) bytes,
MAX(bytes) largest
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
ORDER BY ((a.bytes-b.bytes)/a.bytes) DESC
/