SGA Buffer Information

--
--  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
/