row_number() partition order by 的使用

##取shared pool中每個namespace中佔空間最大的兩個object
set linesize 1000
col name for a50
select *
  from (select row_number() over
         (partition by namespace
           order by sharable_mem desc) row_within
          ,namespace,sharable_mem,substr(name,1,40) name
       from v$db_object_cache
       order by sharable_mem desc
       )
 where row_within <= 2
  order by namespace,row_within;

ROW_WITHIN NAMESPACE                    SHARABLE_MEM NAME
---------- ---------------------------- ------------ --------------------------------------------------
         1 BODY                                83520 PRVT_ADVISOR
         2 BODY                                48508 PRVT_HDM
         1 CLUSTER                               570 C_OBJ#_INTCOL#
         2 CLUSTER                               570 C_FILE#_BLOCK#
         1 CURSOR                             121568 SELECT source,        (case when time_se
         2 CURSOR                             117736 SELECT source,        (case when time_se
         1 INDEX                               10027 WRH$_TABLESPACE_STAT_PK
         2 INDEX                               10021 WRH$_PARAMETER_PK
         1 INVALID NAMESPACE                    2018 WRH$_PARAMETER
         2 INVALID NAMESPACE                    2000 WRH$_SYSSTAT
         1 PUB_SUB                               564 DATABASE

ROW_WITHIN NAMESPACE                    SHARABLE_MEM NAME
---------- ---------------------------- ------------ --------------------------------------------------
         2 PUB_SUB                                 0 BOSS
         1 RSRC PLAN                             565 SYS_GROUP
         2 RSRC PLAN                               0 OTHER_GROUPS
         1 RULESET                                 0 ALERT_QUE_R
         1 TABLE/PROCEDURE                    498764 STANDARD
         2 TABLE/PROCEDURE                    293971 ANYDATA

17 rows selected.