Did you know that you can navigate the posts by swiping left and right?
The following SQL script shows the space consumed by all the user and system tablespaces (including temps). We use this to model tablespace usage for database autotuning.
set pagesize 200
set linesize 200
SELECT d.tablespace_name,
NVL(a.bytes / 1024 / 1024, 0) "Size (MB)",
DECODE(d.contents,'UNDO', NVL(u.bytes, 0)/1024/1024,
NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024) "Used (MB)",
DECODE(d.contents,'UNDO', NVL(u.bytes /a.bytes * 100, 0),
NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)) "Used (%)",
d.status,
d.contents,
d.extent_management,
d.segment_space_management
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes, COUNT(file_id) count
from dba_data_files
GROUP BY tablespace_name) a,
(select tablespace_name,sum(bytes) bytes
from dba_free_space
group by tablespace_name) f,
(SELECT tablespace_name, SUM(bytes) bytes
from dba_undo_extents
WHERE status IN ('ACTIVE','UNEXPIRED')
GROUP BY tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = f.tablespace_name(+) AND
d.tablespace_name = u.tablespace_name(+) AND
NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
UNION ALL
SELECT d.tablespace_name,
NVL(a.bytes/ 1024 / 1024, 0) "Size (MB)",
NVL(t.bytes, 0)/1024/1024 "Used (MB)",
NVL(t.bytes / a.bytes * 100, 0)"Used (%)",
d.status, d.contents,
d.extent_management,
d.segment_space_management
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes, count(file_id) count
from dba_temp_files
group by tablespace_name) a,
(select ss.tablespace_name,
sum((ss.used_blocks*ts.blocksize)) bytes
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = t.tablespace_name(+) AND
d.extent_management ='LOCAL' AND
d.contents = 'TEMPORARY'
ORDER BY 1;
It produces a sample output
e.g. Sample output:
TABLESPACE_NAME Size (MB) Used (MB) Used (%) STATUS CONTENTS EXTENT_MAN SEGMEN
------------------------------ ---------- ---------- ---------- --------- --------- ---------- ------
AR_CUST 150 49.6875 33.125 ONLINE PERMANENT LOCAL AUTO
AR_DIST 3175 3001 94.519685 ONLINE PERMANENT LOCAL AUTO
AR_INDEXES 5289.625 5037.6875 95.2371387 ONLINE PERMANENT LOCAL AUTO
AR_LINES 29869 7792.8125 26.0899679 ONLINE PERMANENT LOCAL AUTO
AR_TABLES 3743.4375 3516.5625 93.9393939 ONLINE PERMANENT LOCAL AUTO
AR_TABLES1 1270.875 1 .078685945 ONLINE PERMANENT LOCAL AUTO
AR_TEMP 17032.9922 100 .587095907 ONLINE TEMPORARY LOCAL MANUAL
AR_TEMP1 200 0 0 ONLINE TEMPORARY LOCAL MANUAL
AR_TRX 2645.25 2470.625 93.3985446 ONLINE PERMANENT LOCAL AUTO
MVIEW 506 481 95.0592885 ONLINE PERMANENT LOCAL AUTO
SMU_ND1 60090 16524.5625 27.499688 ONLINE UNDO LOCAL MANUAL
SMU_ND2 7000 413.875 5.9125 ONLINE UNDO LOCAL MANUAL
SMU_ND3 7000 1874.6875 26.78125 ONLINE UNDO LOCAL MANUAL
SMU_ND4 7000 401.8125 5.74017857 ONLINE UNDO LOCAL MANUAL
SYSAUX 8864.8125 8097.6875 91.3464047 ONLINE PERMANENT LOCAL AUTO
SYSTEM 1000 590.25 59.025 ONLINE PERMANENT LOCAL MANUAL
SYSTEM_TEMP 1145.9375 .6875 .059994546 ONLINE TEMPORARY LOCAL MANUAL
SYSTEM_USER 3000 1121.5 37.3833333 ONLINE PERMANENT LOCAL AUTO