监控索引常用的sql脚本

1、当前用户下,哪些表的索引个数字超过5个的 
SELECT TABLE_NAME, COUNT(*) CNT
  FROM USER_INDEXES
 GROUP BY TABLE_NAME
HAVING COUNT(*) >= 5
 ORDER BY CNT DESC;

2、从未收集过统计信息或者是最近30天内未收集过统计信息的表
SELECT INDEX_NAME, TABLE_NAME, LAST_ANALYZED, NUM_ROWS, TEMPORARY, STATUS
  FROM USER_INDEXES
 WHERE STATUS <> 'N/A'
   AND (LAST_ANALYZED IS NULL OR LAST_ANALYZED < SYSDATE - 30);
   
3、哪些组合索引组合列超过4个的

SELECT TABLE_NAME, INDEX_NAME, COUNT(*) CNT
  FROM USER_IND_COLUMNS
 GROUP BY TABLE_NAME, INDEX_NAME
HAVING COUNT(*) >= 4
 ORDER BY COUNT(*) DESC;

4、大于10GB的表未建任何索引

SELECT SEGMENT_NAME, BYTES/1024/1024/1024 "GB", BLOCKS, TABLESPACE_NAME
  FROM USER_SEGMENTS
 WHERE SEGMENT_TYPE = 'TABLE'
   AND SEGMENT_NAME NOT IN (SELECT TABLE_NAME FROM USER_INDEXES)
   AND BYTES / 1024 / 1024 / 1024 >= 10
 ORDER BY GB DESC;
 
5、失效索引 
SELECT T.INDEX_NAME,
       T.TABLE_NAME,
       BLEVEL,
       T.NUM_ROWS,
       T.LEAF_BLOCKS,
       T.DISTINCT_KEYS
  FROM USER_INDEXES T
 WHERE STATUS = 'UNUSABLE';
 
6、查找在最近30天内未被使用过的索引
SELECT *
  FROM V$OBJECT_USAGE
 WHERE USED = 'NO'
   AND START_MONITORING <= SYSDATE - 30
   AND END_MONITORING IS NOT NULL;
   
7、外键的约束失效了
SELECT TABLE_NAME,
       CONSTRAINT_NAME,
       STATUS,
       CONSTRAINT_TYPE,
       R_CONSTRAINT_NAME
  FROM USER_CONSTRAINTS
 WHERE STATUS='DISABLED';