Oracle tablespace size details


SELECT sumfiles.dfts "Tablespace", 
    round(sumfiles.fullsiz,2) "Allocated MB", 
    round(sumfiles.maxsiz,2) "Max Bytes MB", 
    round(sumfiles.fullsiz-nvl(sumfree.free,0),2) "Used MB",
    round(nvl(sumfree.free,0),2) "Free MB",
    round(((sumfiles.fullsiz - nvl(sumfree.free,0))/sumfiles.fullsiz)*100,2) "% Used", 
    round(((sumfiles.fullsiz - nvl(sumfree.free,0))/sumfiles.maxsiz)*100,2) "% MAX Used" 
FROM 
(SELECT 
    tablespace_name ts, 
    sum(bytes)/1024/1024 free 
FROM dba_free_space 
WHERE tablespace_name not in ('TEMP') 
GROUP BY tablespace_name) sumfree, 
(SELECT 
    tablespace_name dfts, 
    sum(bytes)/1024/1024 fullsiz, 
    sum(decode(maxbytes,0,bytes,maxbytes))/1024/1024 maxsiz 
FROM dba_data_files 
WHERE tablespace_name not in ('TEMP') 
GROUP BY tablespace_name) sumfiles 
WHERE sumfiles.dfts = sumfree.ts (+) 
ORDER BY 1 
/
Tagged:  tablespace size

Comments: Leave Comment

* The email will not be published on the website.