| The size of a (XE) database [2008.01.06] Bookmark (1.0.0.0) After reading in the OTN thread Does tha UNDO Ts count on Database Storage Limit ? I tested my own instance using Paul M.s SQL statement:
SELECT total
, allocated
, allocated / total * 100 percent_free
FROM (SELECT 5120 total
, SUM(bytes) / 1024 / 1024 allocated
FROM dba_data_files
WHERE tablespace_name != 'UNDO'
)
;
TOTAL ALLOCATED PERCENT_FREE
---------- ---------- ------------
5120 1510 29,4921875
Interesting - and a bit surprising. 30% already gone? Taking a closer look, I added the SELECT from my Dynamic (performance) views and reports entry.
SELECT total
, allocated
, allocated / total * 100 percent_used
FROM (SELECT 5120 total
, SUM(allocated) / 1024 / 1024 allocated
FROM dba_data_files df
, (SELECT tablespace_name
, SUM(bytes) allocated
FROM dba_segments
GROUP BY tablespace_name
) sg
WHERE df.tablespace_name = sg.tablespace_name
AND df.tablespace_name <> 'UNDO'
)
;
TOTAL ALLOCATED PERCENT_USED
---------- ---------- ------------
5120 1230,6875 24,0368652
BTW. The major share is taken by the system and sysaux tablespace.
Update I added that SELECT to my System section. |