DB Buffer Usage Query

-- DB Buffer Usage

select decode(state,

            0, 'Free',

            1, decode(lrba_seq, 0, 'Available', 'Being Modified'),     

            2, 'Not Modified',

            3, 'Being Read',

               'Other') block_status,

            count(*) "count"

from sys.x$bh

group by decode(state,

            0, 'Free',

            1, decode(lrba_seq, 0, 'Available', 'Being Modified'),     

            2, 'Not Modified',

            3, 'Being Read',

       'Other');