25 lines
1.0 KiB
SQL
25 lines
1.0 KiB
SQL
-- -----------------------------------------------------------------------------------
|
|
-- File Name : http://www.oracle-base.com/dba/10g/sga_buffers.sql
|
|
-- Author : DR Timothy S Hall
|
|
-- Description : Displays the status of buffers in the SGA.
|
|
-- Requirements : Access to the v$ and DBA views.
|
|
-- Call Syntax : @sga_buffers
|
|
-- Last Modified: 27/07/2005
|
|
-- -----------------------------------------------------------------------------------
|
|
SET LINESIZE 200
|
|
COLUMN object_name FORMAT A30
|
|
|
|
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;
|