Oracle SQL组通过条目

Question

我想写一个sql来获得前5个表空间存储指标。在查询下方给出关于所有tbspace的度量。感激，如果有人微调这只有前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;` 

感谢

Answer 1

可以使用ROWNUM。 Oracle在返回结果后对结果应用rownum。 您需要筛选返回的结果，因此需要子查询。您也可以使用RANK()函数获取Top-N结果。

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;