Listen Software Solutions' "How To" Series:
Calculating Tablespace and Table Byte Sizes
By David Nishimoto
Calculating Tablespace and Table Byte Sizes
Tablespace Byte Sizes
select distinct a.tablespace_name,
sum(a.bytes) as free_bytes,
sum(b.BYTES) as Total_Bytes
from dba_free_space a, dba_data_files b
Where a.tablespace_name = b.tablespace_name group by a.tablespace_name
Table Byte Sizes
select segment_name,tablespace_name,bytes,blocks
FROM DBA_SEGMENTS
where segment_type='TABLE' AND TABLESPACE_NAME
NOT IN('SYSTEM') order by bytes desc
Calculate Tablespace Size for Multiple Datafiles
Tablespace Byte Size
1 tablespace - multiple datafiles
Contribution by Stephen Mitchell
SELECT dfs.tablespace_name tablespace_name,
ddf.total_size total_size,
ddf.total_size - dfs.total_free total_used,
dfs.total_free total_free,
(ddf.total_size - dfs.total_free) / ddf.total_size *
100 cap,
dfs.total_chunks total_chunks,
dfs.largest_chunk largest_chunk
FROM (SELECT a.tablespace_name,
SUM(a.bytes) / 1024 / 1024 total_free,
COUNT(a.bytes) total_chunks,
MAX(a.bytes) / 1024 / 1024 largest_chunk
FROM dba_free_space a
GROUP BY a.tablespace_name) dfs,
(SELECT b.tablespace_name,
SUM(b.bytes) / 1024 / 1024 total_size
FROM dba_data_files b
GROUP BY b.tablespace_name) ddf
WHERE dfs.tablespace_name = ddf.tablespace_name
ORDER BY dfs.tablespace_name
Back to the LSS "How To" Series Main Page
|
