SQL & PL/SQL

Folks,

I am trying to display the list of all the datafiles in relation to the tablespace in 11g. My query is

SELECT Substr(df.tablespace_name, 1, 20) "Tablespace Name",

   Substr(df.file\_name, 1, 80) "File Name",

   Round(df.bytes / 1024 / 1024, 0) "Size (M)",

   decode(e.used\_bytes, NULL, 0, Round(e.used\_bytes/1024/1024, 0)) "Used (M)",

   decode(f.free\_bytes, NULL, 0, Round(f.free\_bytes/1024/1024, 0)) "Free (M)",

   decode(e.used\_bytes,

          NULL,

          0,

          Round((e.used\_bytes / df.bytes) \* 100, 0)) "% Used"

FROM dba_data_files df,

   (SELECT file\_id, sum(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;

In the Output given below we see that the tablespace name users gets displayed twice as it has 2 datafiles.

My requirement is 'in case of multiple datafiles the tablespace name should occur only once not for every row'. Hope my requirement was clear.

I am using 11.2.0.4 in Linux 7

Thanks.