Volumétrie de la base

Pour avoir des information sur la volumétrie de la base :



clear breaks
clear computes
clear columns
SET pagesize 50
SET linesize 120
SET heading ON
COLUMN tablespace_name heading 'Tablespace' justify left format a20 truncated
COLUMN tbsize heading 'Size|(Mb) ' justify left format 9,999,999.99
COLUMN tbused heading 'Used|(Mb) ' justify right format 9,999,999.99
COLUMN tbfree heading 'Free|(Mb) ' justify right format 9,999,999.99
COLUMN tbusedpct heading 'Used % ' justify left format a8
COLUMN tbfreepct heading 'Free % ' justify left format a8
break ON report
compute SUM label 'Totals:' OF tbsize tbused tbfree ON report

SELECT t.tablespace_name, ROUND(a.bytes,2) tbsize,
NVL(ROUND(c.bytes,2),'0') tbfree,
NVL(ROUND(b.bytes,2),'0') tbused,
TO_CHAR(ROUND(100 * (NVL(b.bytes,0)/NVL(a.bytes,1)),2)) || '%' tbusedpct,
TO_CHAR(ROUND(100 * (NVL(c.bytes,0)/NVL(a.bytes,1)),2)) || '%' tbfreepct
FROM dba_tablespaces t,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) bytes
FROM dba_data_files
GROUP BY tablespace_name
UNION
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) bytes
FROM dba_temp_files
GROUP BY tablespace_name ) a,
(SELECT e.tablespace_name, ROUND(SUM(e.bytes)/1024/1024,2) bytes
FROM dba_segments e
GROUP BY e.tablespace_name
UNION
SELECT tablespace_name, SUM(max_size) bytes
FROM v$sort_segment
GROUP BY tablespace_name) b,
(SELECT f.tablespace_name, ROUND(SUM(f.bytes)/1024/1024,2) bytes
FROM dba_free_space f
GROUP BY f.tablespace_name
UNION
SELECT tmp.tablespace_name, (SUM(bytes/1024/1024) - SUM(max_size)) bytes
FROM dba_temp_files tmp, v$sort_segment sort
WHERE tmp.tablespace_name = sort.tablespace_name
GROUP BY tmp.tablespace_name) c
WHERE
t.tablespace_name = a.tablespace_name (+)
AND t.tablespace_name = b.tablespace_name (+)
AND t.tablespace_name = c.tablespace_name (+)
ORDER BY t.tablespace_name;