I am getting you. In all, are you trying to findout free/used space in tablespace? Finding a max extent without segment name doesn't make any use, this is what I feel. If you need to findout what I thought then use this query.
Code:SELECT Substr(df.tablespace_name,1,20) "Tablespace_Name", Substr(df.file_name,1,40) "File_Name", Round(df.bytes/1024/1024,2) "Size_(M)", Round(e.used_bytes/1024/1024,2) "Used_(M)", Round(f.free_bytes/1024/1024,2) "Free_(M)" FROM DBA_DATA_FILES DF, (SELECT file_id, Sum(Decode(bytes,NULL,0,bytes)) used_bytes FROM dba_extents GROUP by file_id) E, (SELECT Max(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f WHERE e.file_id (+) = df.file_id AND df.file_id = f.file_id (+) ORDER BY df.tablespace_name, df.file_name




Reply With Quote