SELECT df.tablespace_name, df.autoextensible, df.allocated_mbytes, df.maximum_mbytes, nvl(fs.free_mbytes,0) free_mbytes, df.allocated_mbytes - nvl(fs.free_mbytes,0) used_mbytes, CASE autoextensible WHEN 'YES' THEN ROUND((df.allocated_mbytes - nvl(fs.free_mbytes,0)) / maximum_mbytes * 100) WHEN 'NO' THEN ROUND((df.allocated_mbytes - nvl(fs.free_mbytes,0)) / allocated_mbytes * 100) END percent_used, ROUND((df.allocated_mbytes - nvl(fs.free_mbytes,0)) / allocated_mbytes * 100) alloc_used FROM ( SELECT tablespace_name, ROUND (SUM(bytes) / 1024 / 1024, 2) allocated_mbytes, autoextensible, ROUND (SUM(maxbytes) / 1024 / 1024, 2) maximum_mbytes FROM DBA_DATA_FILES GROUP BY tablespace_name, autoextensible ) df, ( SELECT tablespace_name, ROUND (SUM(bytes) / 1024 / 1024, 2) free_mbytes FROM DBA_FREE_SPACE GROUP BY tablespace_name ) fs WHERE df.tablespace_name = fs.tablespace_name (+) ORDER BY tablespace_name; |