outer join not responding as expected

From: Ben <balvey_at_comcast.net>
Date: 15 Feb 2006 07:26:25 -0800
Message-ID: <1140017185.165702.85550@g43g2000cwa.googlegroups.com>

I have modified a sql statement that I have seen in various places (ask tom, google, etc) to try and see the overall size and available space, along with the max size of my existing datafiles.

SELECT trim(b.file_name) "DATAFILE",

b.tablespace_name,
a.PIECES,
a.FREE,
round(b.bytes/1024/1024,2) "DF SIZE MB",
round(b.maxbytes/1024/1024,2) "DF Max MB",

round(b.maxbytes/1024/1024 - b.bytes/1024/1024,2) "DF FREE MB" FROM (
SELECT file_id,
count(*) PIECES,
round(sum(blocks)*8192/1024/1024,2) FREE

FROM DBA_FREE_SPACE
GROUP BY file_id
) a, dba_data_files b

WHERE a.file_id = b.file_id (+)
ORDER BY 2, 4 I would expect this to return all the datafiles that are in dba_data_files, even if they don't have any free space but it doesn't. The datafiles that are full don't show up. what am I overlooking?

Ben Received on Wed Feb 15 2006 - 09:26:25 CST