SELECT o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
COUNT (DISTINCT file# || block#) num_blocks
FROM dba_objects o, v$bh bh
WHERE o.data_object_id = bh.objd
AND o.owner NOT IN ('SYS', 'SYSTEM')
AND bh.status != 'free'
GROUP BY o.owner,
o.object_name,
o.subobject_name,
o.object_type
ORDER BY COUNT (DISTINCT file# || block#) DESC;
No comments:
Post a Comment