select
  decode(state, 0 ,'Free',
                1 ,'Read and Modified',
                2 ,'Read and Not Modified',
                3 ,'Currently Being Read',
                  'Other') as name,
  count(*) as cnt
 from sys.v_bh
 group by 
  decode(state, 0 ,'Free',
                1 ,'Read and Modified',
                2 ,'Read and Not Modified',
                3 ,'Currently Being Read',
                  'Other')

select
 b.object_name,
 count(*) as cnt
FROM
 sys.v_bh a,
 dba_objects b
where
 a.obj != 0  and
 a.obj = b.data_object_id
group by b.object_name
order by count(*) desc

oracle     7514    7513    0   10 : 34  pts/ 0      00 : 01 : 27  sqlplus            
oracle     7967       1    0   12 : 58  ?         00 : 00 : 00  ora_pmon_epsid01
oracle     7969       1    0   12 : 58  ?         00 : 00 : 00  ora_dbw0_epsid01
oracle     7971       1    0   12 : 58  ?         00 : 00 : 03  ora_lgwr_epsid01
oracle     7973       1    0   12 : 58  ?         00 : 00 : 00  ora_ckpt_epsid01
oracle     7975       1    0   12 : 58  ?         00 : 00 : 00  ora_smon_epsid01
oracle     7977       1    0   12 : 58  ?         00 : 00 : 00  ora_reco_epsid01
oracle     7978    7514    7   12 : 58  ?         00 : 02 : 41  oracleepsid01 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))

select 
  '['||a.sid||']'||a.program program,
  a.username, 
  a.schemaname,
  n.class,
  n.name,
  s.value
 from
  v$session a,
  v$sesstat s,
  v$statname n
 where
  a.sid = s.sid and
  s.statistic# = n.statistic# and
  s.value!= 0 
order by a.sid, n.class, s.statistic#

select 
  n.name,
  trunc(sum(s.value)/ 1024 ) as value
 from
  v$sesstat s,
  v$statname n
 where
  n.name in ('session pga memory','session pga memory max') and
  s.statistic# = n.statistic# 
 group by n.name

SQLWKS> select 
      2 >   n.name,
      3 >   trunc(sum(s.value)/ 1024 ) as value
      4 >  from
      5 >   v$sesstat s,
      6 >   v$statname n
      7 >  where
      8 >   n.name in ('session pga memory','session pga memory max') and
      9 >   s.statistic# = n.statistic# 
     10 >  group by n.name
     11 > 
NAME                                                             VALUE     
 ---------------------------------------------------------------- ----------
 
session pga memory                                                     27172 
session pga memory max                                                 27243 
Выбрано  2  строк.