##取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.

<这里需要补充对row_number()函数的解释及它的使用场景>