檢查外來鍵是否有索引的指令碼

壹頁書發表於2014-06-05
索引指令碼

  1. COLUMN COLUMNS format a30 word_wrapped
  2. COLUMN tablename format a15 word_wrapped
  3. COLUMN constraint_name format a15 word_wrapped
  4. SELECT TABLE_NAME,
  5.        CONSTRAINT_NAME,
  6.        CNAME1 || NVL2(CNAME2, ',' || CNAME2, NULL) ||
  7.        NVL2(CNAME3, ',' || CNAME3, NULL) ||
  8.        NVL2(CNAME4, ',' || CNAME4, NULL) ||
  9.        NVL2(CNAME5, ',' || CNAME5, NULL) ||
  10.        NVL2(CNAME6, ',' || CNAME6, NULL) ||
  11.        NVL2(CNAME7, ',' || CNAME7, NULL) ||
  12.        NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS
  13.   FROM (SELECT B.TABLE_NAME,
  14.                B.CONSTRAINT_NAME,
  15.                MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,
  16.                MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,
  17.                MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
  18.                MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,
  19.                MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
  20.                MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,
  21.                MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,
  22.                MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,
  23.                COUNT(*) COL_CNT
  24.           FROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,
  25.                        SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,
  26.                        SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,
  27.                        POSITION
  28.                   FROM USER_CONS_COLUMNS) A,
  29.                USER_CONSTRAINTS B
  30.          WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
  31.            AND B.CONSTRAINT_TYPE = 'R'
  32.          GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONS
  33.  WHERE COL_CNT > ALL
  34.  (SELECT COUNT(*)
  35.           FROM USER_IND_COLUMNS I
  36.          WHERE I.TABLE_NAME = CONS.TABLE_NAME
  37.            AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,
  38.                 CNAME6, CNAME7, CNAME8)
  39.            AND I.COLUMN_POSITION <= CONS.COL_CNT
  40.          GROUP BY I.INDEX_NAME)
  41. /
在上面的基礎上修改了一下，可以檢查所有的使用者。

  1. SET linesize 400;
  2. COLUMN OWNER format a10 word_wrapped
  3. COLUMN COLUMNS format a30 word_wrapped
  4. COLUMN TABLE_NAME format a15 word_wrapped
  5. COLUMN CONSTRAINT_NAME format a40 word_wrapped
  6. SELECT OWNER,
  7.      TABLE_NAME,
  8.        CONSTRAINT_NAME,
  9.        CNAME1 || NVL2(CNAME2, ',' || CNAME2, NULL) ||
  10.        NVL2(CNAME3, ',' || CNAME3, NULL) ||
  11.        NVL2(CNAME4, ',' || CNAME4, NULL) ||
  12.        NVL2(CNAME5, ',' || CNAME5, NULL) ||
  13.        NVL2(CNAME6, ',' || CNAME6, NULL) ||
  14.        NVL2(CNAME7, ',' || CNAME7, NULL) ||
  15.        NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS
  16.   FROM (SELECT B.OWNER,B.TABLE_NAME,
  17.                B.CONSTRAINT_NAME,
  18.                MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,
  19.                MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,
  20.                MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
  21.                MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,
  22.                MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
  23.                MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,
  24.                MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,
  25.                MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,
  26.                COUNT(*) COL_CNT
  27.           FROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,
  28.                        SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,
  29.                        SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,
  30.                        POSITION
  31.                   FROM DBA_CONS_COLUMNS WHERE OWNER NOT IN ('SYS','SYSTEM','SYSMAN','HR','OE','EXFSYS','DBSNMP','MDSYS','OLAPSYS','SCOTT','EXFSYS','SH','PM','CTXSYS')) A,
  32.                DBA_CONSTRAINTS B
  33.          WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
  34.            AND B.CONSTRAINT_TYPE = 'R'
  35.          GROUP BY B.OWNER,B.TABLE_NAME, B.CONSTRAINT_NAME) CONS
  36.  WHERE COL_CNT > ALL
  37.  (SELECT COUNT(*)
  38.           FROM DBA_IND_COLUMNS I
  39.          WHERE I.TABLE_NAME = CONS.TABLE_NAME AND I.TABLE_OWNER=CONS.OWNER
  40.            AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,
  41.                 CNAME6, CNAME7, CNAME8)
  42.            AND I.COLUMN_POSITION <= CONS.COL_CNT
  43.          GROUP BY I.INDEX_NAME)
  44. /


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1176022/，如需轉載，請註明出處，否則將追究法律責任。

相關文章