Oracle SQL Group by Clause

Question

I would like to write a sql to get top 5 table space storage metric. Below query gives the metric about all tbspaces. Appreciate if someone fine tune this to have only top N

SELECT
ts.tablespace_name AS TBNAME,
round((ts.tablespace_size/1024/1024),2) AS SIZE_MB,
round((ts.tablespace_used_size/1024/1024),2) AS USED_MB,
round(((ts.tablespace_size - ts.tablespace_used_size)/1024/1024),2) AS     FREE_MB
FROM
mgmt$db_tablespaces ts,
(SELECT d.target_guid, d.tablespace_name, count(d.file_name) df_count,
sum(decode(d.autoextensible, 'YES', 1, 0)) auto_extend
FROM mgmt$db_datafiles d, mgmt$target t
WHERE t.target_guid = '<id>' AND
(t.target_type='rac_database' OR
(t.target_type='oracle_database' AND t.TYPE_QUALIFIER3 != 'RACINST')) AND
t.target_guid = d.target_guid
GROUP BY d.target_guid, d.tablespace_name) df
WHERE
ts.target_guid = df.target_guid AND
df.tablespace_name = ts.tablespace_name
ORDER BY ts.tablespace_size;`

Thanks

Mahesh · Answer 1 · 2015-02-12T11:35:27.680

You can use the ROWNUM. Oracle applies rownum to the result after it has been returned. You need to filter the result after it has been returned, so a subquery is required. You can also use RANK() function to get Top-N results.

  SELECT 
       * 
  FROM
      (
         SELECT
            ts.tablespace_name AS TBNAME,
            round((ts.tablespace_size/1024/1024),2) AS SIZE_MB,
            round((ts.tablespace_used_size/1024/1024),2) AS USED_MB,
            round(((ts.tablespace_size - ts.tablespace_used_size)/1024/1024),2) AS     FREE_MB
       FROM
          mgmt$db_tablespaces ts,
          (SELECT d.target_guid, d.tablespace_name, count(d.file_name) df_count,
              sum(decode(d.autoextensible, 'YES', 1, 0)) auto_extend
            FROM mgmt$db_datafiles d, mgmt$target t
            WHERE t.target_guid = '<id>' AND
            (t.target_type='rac_database' OR
            (t.target_type='oracle_database' AND t.TYPE_QUALIFIER3 != 'RACINST')) AND
            t.target_guid = d.target_guid
           GROUP BY d.target_guid, d.tablespace_name) df
       WHERE
        ts.target_guid = df.target_guid AND
        df.tablespace_name = ts.tablespace_name
       ORDER BY ts.tablespace_size
      )
  WHERE ROWNUM <= 5;

What do you mean by this doesn't work what error do you get ? — Mahesh, Feb 12 '15 at 07:40
thats not a valid SQL. I have added RANK() and got the output i wanted. — Prakash, Feb 12 '15 at 09:41
The semicolon is in the wrong place and there is a quote sign next to it, which must be removed. A simple copy & paste mistake. That's the only syntax error I can spot here. — Thorsten Kettner, Feb 12 '15 at 11:34
You would usually not use RANK for a top-n query, but DENSE_RANK or ROW_NUMBER (though RANK is still a valid option). @Prakash: If you want to use any of the three, think about which one is the appropriate one for your case. — Thorsten Kettner, Feb 12 '15 at 11:39

Oracle SQL Group by Clause

1 Answers1