1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
|
col Total_Mo for 999,999.90
col Total_Mo_Max_size for 999,999.90 heading 'Total Mo|Max Size'
col Total_Mo_Actual_size for 999,999.90 heading 'Total Mo|Actual Size'
col Total_Mo for 999,999.90
col Free_Mo for 999,999.90
col Used_Mo for 999,999.90
col Used_%_with_maxsize heading 'Used %|with maxsize'
col Used_%_without_maxsize heading 'Used %|without maxsize'
SELECT T.tablespace_name,
TS."Total_b"/1048576 AS "Total_Mo_Max_size",
TS."Total_c"/1048576 AS "Total_Mo_Actual_size",
NVL(FS."Free_b"/1048576, 0) AS "Free_Mo",
NVL(US."Used_b"/1048576, 0) AS "Used_Mo",
NVL(Round( 100 * US."Used_b" / TS."Total_b", 2 ), 0) AS "Used_%_with_maxsize",
NVL(Round( 100 * US."Used_b" / TS."Total_c", 2 ), 0) AS "Used_%_without_maxsize"
FROM dba_tablespaces T,
(SELECT tablespace_name,
Round(Sum(Bytes), 0) AS "Total_c",
Round(Sum(decode(MaxBytes,0,bytes,maxbytes)), 0) AS "Total_b"
FROM dba_data_files
GROUP BY tablespace_name) TS,
(SELECT tablespace_name,
Round(Sum(Bytes), 0) AS "Free_b"
FROM dba_free_space
GROUP BY tablespace_name) FS,
(SELECT tablespace_name,
Round(Sum(Bytes), 0) AS "Used_b"
FROM dba_segments
GROUP BY tablespace_name) US
WHERE T.tablespace_name = TS.tablespace_name
AND T.tablespace_name = FS.tablespace_name (+)
AND T.tablespace_name = US.tablespace_name (+)
order by 1; |
Partager