Calculate Size of Tablespace, Free space of a Tablespace

Calculate Size of Tablespace, Free space of a Tablespace

How to find the free space in a tablespace ? How to find the free space in undo tablespace? how to find size of System tablespace ?

Let us explore the above things with three data dictionary views

DBA_TABLESPACES
DBA_DATA_FILES
DBA_FREE_SPACE

DBA_TABLESPACES dictionary view provides the list of all tablespaces present in the database

.

DBA_DATA_FILES dictionary view provides the list of all physical data files and their size. DBA_DATA_FILES also provides the path of the data files in the system.Each data file is associated with only one tablespace. But a tablespace is associated with more than one data file.”Bytes” column present in DBA_DATA_FILES denote the total bytes allocated to a data file.

DBA_FREE_SPACE dictionary view provides the free space information associated with each data file.”Bytes” column present in DBA_free_space denote the total free space in bytes available in a data file.

Below query calculates the total file size allocated from dba_data_files view, total free space from dba_free_space view, and then calculates the sum by grouping the data files associated with a tablespace.

To display the data in mega bytes (MB), all the columns are divided by 1048576 and rounded to two digits.

Tot_pct_free determines the free space in percentage terms.
Tot_pct_used determines the used space in percentage terms.


SELECT * FROM (
SELECT c.tablespace_name,
ROUND(a.bytes/1048576,2) MB_Allocated,
ROUND(b.bytes/1048576,2) MB_Free,
ROUND((a.bytes-b.bytes)/1048576,2) MB_Used,
ROUND(b.bytes/a.bytes * 100,2) tot_Pct_Free,
ROUND((a.bytes-b.bytes)/a.bytes,2) * 100 tot_Pct_Used
FROM (SELECT tablespace_name,
SUM(a.bytes) bytes
FROM sys.DBA_DATA_FILES a
GROUP BY tablespace_name) a,
(SELECT a.tablespace_name,
NVL(SUM(b.bytes),0) bytes
FROM sys.DBA_DATA_FILES a,
sys.DBA_FREE_SPACE b
WHERE a.tablespace_name = b.tablespace_name (+)
AND a.file_id = b.file_id (+)
GROUP BY a.tablespace_name) b,
sys.DBA_TABLESPACES c
WHERE a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name = c.tablespace_name
) WHERE tot_Pct_Used >=0
ORDER BY tablespace_name