Showing posts with label Rollback. Show all posts
Showing posts with label Rollback. Show all posts

Tuesday, July 29, 2014

Query to find buffer cache details of data, rollback information

col class form A10
select decode(greatest(class,10),10,decode(class,1,'Data',2 ,'Sort',4,'Header',to_char(class)),'Rollback') "Class",
sum(decode(bitand(flag,1),1,0,1)) "Not Dirty",
sum(decode(bitand(flag,1),1,1,0)) "Dirty",
sum(dirty_queue) "On Dirty",
count(*) "Total"
from x$bh
group by decode(greatest(class,10),10,decode(class,1,'Data',2 ,'Sort',4,'Header',to_char(class)),'Rollback')
/

Note -> This is query on Oracle internal table x$