--

--  SGA buffers info

--

SET PAUSE ON

SET PAUSE 'Press Return to Continue'

SET HEADING ON

SET LINESIZE 300

SET PAGESIZE 60

COLUMN object_name FORMAT A32

SELECT t.name AS tablespace_name,

o.object_name,

SUM(DECODE(bh.status, 'free', 1, 0)) AS free,

SUM(DECODE(bh.status, 'xcur', 1, 0)) AS xcur,

SUM(DECODE(bh.status, 'scur', 1, 0)) AS scur,

SUM(DECODE(bh.status, 'cr', 1, 0)) AS cr,

SUM(DECODE(bh.status, 'read', 1, 0)) AS read,

SUM(DECODE(bh.status, 'mrec', 1, 0)) AS mrec,

SUM(DECODE(bh.status, 'irec', 1, 0)) AS irec

FROM   v$bh bh

JOIN dba_objects o ON o.object_id = bh.objd

JOIN v$tablespace t ON t.ts# = bh.ts#

GROUP BY t.name, o.object_name

/