how come this SQL statement does not reflect the TEMP tablespace

[ashley75]

Hi all

I have this SQL statement and it doesn't reflect my TEMP tablespace.

select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used",round((f.free/a.total)*100) "% Free" from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) used from dba_extents group by tablespace_name) u,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name;

moreover, I also noticed that if any tablespace having no objects in there, it also doesn't reflect on here.

Anyone had any others Query or suggestions to this query??

Thanks

[svk]

Do an outer join and you will see what you wish to.

[chao_ping]

hi£¬
in 8i+, temporary file does not appear in dba_data_files , it is in dba_temp_files.