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??
Do an outer join and you will see what you wish to.
in 8i+, temporary file does not appear in dba_data_files , it is in dba_temp_files.