Oracle View tablespace Usage



View 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 "spare 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 size of each table in a table space

Select Segment_name, Isize | | M
From (select Segment_name, sum (bytes)/1024/1024 isize
From Dba_segments
where tablespace_name = ' snbst_hist '
and Segment_type = ' TABLE '
GROUP by Segment_name)
ORDER BY isize Desc;

View tablespace file Usage size

SELECT b.tablespace_name table Space,
B.file_name Physical file name,
b.bytes/1024/1024 size M,
(B.bytes-sum (NVL (a.bytes, 0))/1024/1024 used M,
SUBSTR ((B.bytes-sum (NVL (a.bytes, 0))/(b.bytes) * 100, 1, 5) utilization
From Dba_free_space A, Dba_data_files B
WHERE a.file_id = b.file_id
GROUP by B.tablespace_name, B.file_name, b.bytes
ORDER by B.tablespace_name;

Oracle View tablespace Usage