How to List Tables with more then 5 indexes in Oracle Posted on November 26, 2018 by SAP Basis Admin select OWNER, TABLE_NAME, COUNT(*) from dba_indexes where OWNER not in (‘SYS’,‘SYSTEM’) group by OWNER, TABLE_NAME having COUNT(*) > 5 order by COUNT(*) desc, OWNER, TABLE_NAME;
Script to find used Buffer Cache Block per Object Posted on November 26, 2018 by SAP Basis Admin SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS FROM DBA_OBJECTS o, V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OWNER != ‘SYS’ GROUP BY o.OBJECT_NAME ORDER BY COUNT(*);