Your query is needlessly complicated, and has the aggregation messed up.

The views dba_data_files, dba_temp_files and v$temp_space_header all have one row per data file per tablespace. So, you need to aggregate the values based on tablespace name.

Similarly, dba_free_space has one row per "block" of free space per tablespace. So, if your apps_ts tablespace has say 4 blocks of free space, then it will have 4 rows for the apps_ts tablespace. Again, you need to aggregate based on tablespace name before doing anything else.

The temp tablespace will almost never have any free space because if the way Oracle works. Once an extent is created in the temp tablespace it is never deallocated. When the transaction that "created" the extent is finished using it, it is marked as being available for use by other sessions, but is no longer free space.

The query below does what I believe you are looking for for the regular tablespaces. If you really need to show the temp tablespace, then you can add a third query to the union all sub-query to look at dba_temp_files. If there is any free space in the temp tablespace it will show up in dba_free_space.

Without seeing what you actually have, I can only guess, but as I said, you have the aggregations messed up in both halves of your query.

The second half of your original query (below the UNION ALL) is the bit that calculates the space for the temp tablespace. It looks to me as if you have multiple files in your temp tablespace and that the files actually have at least two different sizes. This is from one of my databases the has that situation, and builds up to the query that you are using to get the temporary tablespace information.

The basic join of your two queries without the aggregation. I added the file_id and bytes columns from dba_temp_files and the bytes_free and bytes_used columns from v$temp_space_header to make what is happening clearer.