Table Space Common SQL rollup

Table Space Fragmentation Related:
Select Tablespace_name,
Round (sqrt (max (blocks)/sum (blocks)) *
(100/sqrt (sqrt (count (blocks))),
2) FSFI,
(case
When sqrt (max (blocks)/sum (blocks)) *
(100/sqrt (sqrt (count (blocks))) > =
Normal
When sqrt (max (blocks)/sum (blocks)) *
(100/sqrt (sqrt (count (blocks))) <
' Table space fragmentation is high, please tidy '
End) Prompt
From Dba_free_space
GROUP BY Tablespace_name
Order by 2;
--Exclude Sysaux and UNDOTBS1

Defragment: Alter TABLESPACE COALESCE;

To view individual data file usage:
SELECT DISTINCT D.name,
T.name,
Round (S.bytes/(1024x768), 0) Total_space,
Round (S.bytes/(1024x768), 0)-round (f.bytes/(1024x768), 0) used
From V$tablespace T, V$datafile D, Dba_data_files S, Dba_free_space F
where t.ts# = d.ts#
and d.name = S.file_name
and s.file_id = f.file_id
and d.file# = f.file_id
and t.name = F.tablespace_name

Tablespace usage:
Select UPPER (f.tablespace_name) "Table space name",
D.TOT_GROOTTE_MB "Table space size (M)",
D.tot_grootte_mb-f.total_bytes "used Space (M)",
To_char (ROUND ((d.tot_grootte_mb-f.total_bytes)/D.TOT_GROOTTE_MB * 100, 2), ' 990.99 ') | | '% ' "use ratio (%)",
F.total_bytes "free Space (M)",
F.max_bytes "Max Block (M)"
From (SELECT Tablespace_name,
ROUND (SUM (BYTES)/(1024x768 * 1024x768), 2) Total_bytes,
ROUND (MAX (BYTES)/(1024x768), 2) max_bytes
From SYS. Dba_free_space
GROUP by Tablespace_name) F,
(SELECT DD.) Tablespace_name,
ROUND (SUM (DD). BYTES)/(1024x768 * 1024x768), 2) TOT_GROOTTE_MB
From SYS. Dba_data_files DD
GROUP by DD. Tablespace_name) D
WHERE D.tablespace_name = F.tablespace_name
ORDER by 4 desc;

To view the actual remaining space for a table space sql:
Select (
NVL ((select sum (t.maxblocks*8/1024) from Dba_data_files t where t.autoextensible= ' YES ' and t.tablespace_name = ' SYSTEM ') , 0)
+
NVL ((select sum (t.blocks*8/1024) from Dba_data_files t where t.autoextensible= ' NO ' and t.tablespace_name = ' SYSTEM '), 0))
-
（
Select D.tot_grootte_mb-f.total_bytes "Used Space (M)"
From (SELECT Tablespace_name,
ROUND (SUM (BYTES)/(1024x768 * 1024x768), 2) Total_bytes,
ROUND (MAX (BYTES)/(1024x768), 2) max_bytes
From SYS. Dba_free_space
GROUP by Tablespace_name) F,
(SELECT DD.) Tablespace_name,
ROUND (SUM (DD). BYTES)/(1024x768 * 1024x768), 2) TOT_GROOTTE_MB
From SYS. Dba_data_files DD
GROUP by DD. Tablespace_name) D
WHERE d.tablespace_name = f.tablespace_name and d.tablespace_name = ' SYSTEM '
) "Actual remaining space (M)"
from dual;

Data File Usage Rate:
Select tot.file_name,100* (tot.total-free.total)/tot.total| | ' % ' from
(select File_name,file_id,sum (bytes) as total from Dba_data_files tot Group by file_id,file_name) tot,
(select File_id,sum (bytes) as total from Dba_free_space free GROUP by file_id) free
where tot.file_id=free.file_id

Tested to see data file usage, better performance, more accurate:
SELECT B.tablespace_name,
B.file_name,
ROUND (b.bytes/1024/1024/1024, 2) "SIZE GB",
ROUND (b.maxbytes/1024/1024/1024, 2) "MAX SIZE GB",
ROUND (SUM (a.bytes)/1024/1024/1024, 2) "Free SPACE GB",
Case
When maxbytes = 0
Then
ROUND (SUM (a.bytes)/1024/1024/1024, 2)
ELSE
ROUND (
((b.maxbytes-b.bytes) + SUM (a.bytes))/1024/1024/1024, 2)
END
"Total free SPACE GB"
From Dba_free_space A, Dba_data_files B
WHERE a.file_id = b.file_id and B.tablespace_name in (' Cbam ')
GROUP by B.tablespace_name,
B.file_name,
B.bytes,
B.maxbytes
ORDER by 1, 2;

To view the usage of the temp table space:
SELECT A.tablespace_name,
a.bytes/1024/1024 "Total (M)",
(A.BYTES-NVL (b.bytes, 0))/1024/1024 "Free (M)"
From (SELECT tablespace_name, SUM (bytes) bytes
From Dba_temp_files
GROUP by Tablespace_name) A,
(SELECT tablespace_name, SUM (bytes_cached) bytes
From V$temp_extent_pool
GROUP by Tablespace_name) b
WHERE A.tablespace_name = b.tablespace_name (+)

Table Space Common SQL rollup