1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
| select num_blocks,owner,object_name,object_type,object_id,created from (
select * from (
select count(distinct relative_fno||'.'||block_id) num_blocks,object_id from (
select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.ICOL$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.IND$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.COL$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.CLU$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.TAB$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.LOB$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.COLTYPE$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.SUBCOLTYPE$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.NTAB$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.REFCON$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.OPQTYPE$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.ICOLDEP$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.VIEWTRCOL$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.LIBRARY$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.ASSEMBLY$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.ATTRCOL$
union all select obj# object_id,dbms_rowid.rowid_relative_fno(rowid) relative_fno,dbms_rowid.rowid_block_number(rowid) block_id from sys.type_misc$
) group by object_id having count(distinct relative_fno||'.'||block_id) > 2
order by num_blocks desc
) where rownum <100
) join dba_objects using(object_id) order by num_blocks desc,object_id
/ |