[20181122]了解数据库缓存被那些对象占用.txt
--//没事,随手写一个脚本,看看数据库缓存被那些对象占用:
column object_name format a30
column owner format a20
column number_MB format 99999999.99
SELECT *
FROM ( SELECT o.owner,
o.object_name,
COUNT (*) number_of_blocks,
COUNT (DISTINCT FILE# || '.' || BLOCK#) distinct_block_count,
sum (decode(dirty,'Y',1,0)) dirty_block,
COUNT (*) - COUNT (DISTINCT FILE# || '.' || BLOCK#)
diff_number_block,
ROUND (COUNT (*) * 8 / 1024, 2) number_Mb, ROUND ( (ratio_to_report (SUM (1)) OVER () * 100),2) rr
FROM dba_objects o, v$bh v
WHERE o.data_object_id = v.objd AND o.owner != 'SYS' and v.status <> 'free'
GROUP BY o.owner, o.object_name
ORDER BY COUNT (*) DESC)
WHERE ROWNUM <= 50;
--//我直接按照8k数据块计算.取前50个对象.
--//顺便提一下,对于cluster table对象无效.不过一般应用很少应用这种类型的表.
--//把条件o.owner != 'SYS'改写成o.owner = 'SYS',就明白什么回事.
column object_name format a30
column owner format a20
column number_MB format 99999999.99
SELECT *
FROM ( SELECT o.owner,
o.object_name,
v.objd,
COUNT (*) number_of_blocks,
COUNT (DISTINCT FILE# || '.' || BLOCK#) distinct_block_count,
sum (decode(dirty,'Y',1,0)) dirty_block,
COUNT (*) - COUNT (DISTINCT FILE# || '.' || BLOCK#)
diff_number_block,
ROUND (COUNT (*) * 8 / 1024, 2) number_Mb, ROUND ( (ratio_to_report (SUM (1)) OVER () * 100),2) rr
FROM dba_objects o, v$bh v
WHERE o.data_object_id = v.objd AND o.owner = 'SYS' and v.status <> 'free'
GROUP BY o.owner, o.object_name,v.objd
ORDER BY COUNT (*) DESC)
WHERE ROWNUM <= 50;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2221076/,如需转载,请注明出处,否则将追究法律责任。