SCRIPT TO CHECK FOR FOREIGN KEY LOCKING ISSUES

Description

This script checks the current users Foreign Keys to make sure of the
following:

1) All the FK columns have indexes to prevent a possible locking


2) Checks the ORDER OF THE INDEXED COLUMNS. To prevent the locking
problem the columns MUST be indexed in the same order as the FK is
defined.

3) If the script finds a mismatch, the script reports the correct
order of columns that need to be added to prevent the locking
problem.


create table ck_log (
LineNum number,
LineMsg varchar2(2000));

/* Formatted on 2011/09/27 13:08 (Formatter Plus v4.8.7) */
DECLARE
   t_constraint_type     user_constraints.constraint_type%TYPE;
   t_constraint_name     user_constraints.constraint_name%TYPE;
   t_table_name          user_constraints.table_name%TYPE;
   t_r_constraint_name   user_constraints.r_constraint_name%TYPE;
   tt_constraint_name    user_cons_columns.constraint_name%TYPE;
   tt_table_name         user_cons_columns.table_name%TYPE;
   tt_column_name        user_cons_columns.column_name%TYPE;
   tt_position           user_cons_columns.POSITION%TYPE;
   tt_dummy              NUMBER;
   tt_dummychar          VARCHAR2 (2000);
   l_cons_found_flag     VARCHAR2 (1);
   err_table_name        user_constraints.table_name%TYPE;
   err_column_name       user_cons_columns.column_name%TYPE;
   err_position          user_cons_columns.POSITION%TYPE;
   tlinenum              NUMBER;

   CURSOR usertabs
   IS
      SELECT   table_name
          FROM user_tables
      ORDER BY table_name;

   CURSOR tablecons
   IS
      SELECT   constraint_type, constraint_name, r_constraint_name
          FROM user_constraints
         WHERE owner = USER
           AND table_name = t_table_name
           AND constraint_type = 'R'
      ORDER BY table_name, constraint_name;

   CURSOR concolumns
   IS
      SELECT   constraint_name, table_name, column_name, POSITION
          FROM user_cons_columns
         WHERE owner = USER AND constraint_name = t_constraint_name
      ORDER BY POSITION;

   CURSOR indexcolumns
   IS
      SELECT   table_name, column_name, POSITION
          FROM user_cons_columns
         WHERE owner = USER AND constraint_name = t_constraint_name
      ORDER BY POSITION;

   debuglevel            NUMBER                                    := 99;
                                                    -- >>> 99 = dump all info`
   debugflag             VARCHAR (1)                               := 'N';
                                                          -- Turn Debugging on
   t_error_found         VARCHAR (1);
BEGIN
   tlinenum := 1000;

   OPEN usertabs;

   LOOP
      FETCH usertabs
       INTO t_table_name;

      t_error_found := 'N';
      EXIT WHEN usertabs%NOTFOUND;
-- Log current table
      tlinenum := tlinenum + 1;

      INSERT INTO ck_log
                  (linenum, linemsg
                  )
           VALUES (tlinenum, NULL
                  );

      tlinenum := tlinenum + 1;

      INSERT INTO ck_log
                  (linenum, linemsg
                  )
           VALUES (tlinenum, 'Checking Table ' || t_table_name
                  );

      l_cons_found_flag := 'N';

      OPEN tablecons;

      LOOP
         FETCH tablecons
          INTO t_constraint_type, t_constraint_name, t_r_constraint_name;

         EXIT WHEN tablecons%NOTFOUND;

         IF (debugflag = 'Y' AND debuglevel >= 99)
         THEN
            BEGIN
               tlinenum := tlinenum + 1;

               INSERT INTO ck_log
                           (linenum,
                            linemsg
                           )
                    VALUES (tlinenum,
                            'Found CONSTRAINT_NAME = ' || t_constraint_name
                           );

               tlinenum := tlinenum + 1;

               INSERT INTO ck_log
                           (linenum,
                            linemsg
                           )
                    VALUES (tlinenum,
                            'Found CONSTRAINT_TYPE = ' || t_constraint_type
                           );

               tlinenum := tlinenum + 1;

               INSERT INTO ck_log
                           (linenum,
                            linemsg
                           )
                    VALUES (tlinenum,
                            'Found R_CONSTRAINT_NAME = '
                            || t_r_constraint_name
                           );

               COMMIT;
            END;
         END IF;

         OPEN concolumns;

         LOOP
            FETCH concolumns
             INTO tt_constraint_name, tt_table_name, tt_column_name,
                  tt_position;

            EXIT WHEN concolumns%NOTFOUND;

            IF (debugflag = 'Y' AND debuglevel >= 99)
            THEN
               BEGIN
                  tlinenum := tlinenum + 1;

                  INSERT INTO ck_log
                              (linenum, linemsg
                              )
                       VALUES (tlinenum, NULL
                              );

                  tlinenum := tlinenum + 1;

                  INSERT INTO ck_log
                              (linenum,
                               linemsg
                              )
                       VALUES (tlinenum,
                               'Found CONSTRAINT_NAME = '
                               || tt_constraint_name
                              );

                  tlinenum := tlinenum + 1;

                  INSERT INTO ck_log
                              (linenum,
                               linemsg
                              )
                       VALUES (tlinenum,
                               'Found TABLE_NAME = ' || tt_table_name
                              );

                  tlinenum := tlinenum + 1;

                  INSERT INTO ck_log
                              (linenum,
                               linemsg
                              )
                       VALUES (tlinenum,
                               'Found COLUMN_NAME = ' || tt_column_name
                              );

                  tlinenum := tlinenum + 1;

                  INSERT INTO ck_log
                              (linenum, linemsg
                              )
                       VALUES (tlinenum, 'Found POSITION = ' || tt_position
                              );

                  COMMIT;
               END;
            END IF;

            BEGIN
               SELECT 1
                 INTO tt_dummy
                 FROM user_ind_columns
                WHERE table_name = tt_table_name
                  AND column_name = tt_column_name
                  AND column_position = tt_position;

               IF (debugflag = 'Y' AND debuglevel >= 99)
               THEN
                  BEGIN
                     tlinenum := tlinenum + 1;

                     INSERT INTO ck_log
                                 (linenum, linemsg
                                 )
                          VALUES (tlinenum, 'Row Has matching Index'
                                 );
                  END;
               END IF;
            EXCEPTION
               WHEN TOO_MANY_ROWS
               THEN
                  IF (debugflag = 'Y' AND debuglevel >= 99)
                  THEN
                     BEGIN
                        tlinenum := tlinenum + 1;

                        INSERT INTO ck_log
                                    (linenum, linemsg
                                    )
                             VALUES (tlinenum, 'Row Has matching Index'
                                    );
                     END;
                  END IF;
               WHEN NO_DATA_FOUND
               THEN
                  IF (debugflag = 'Y' AND debuglevel >= 99)
                  THEN
                     BEGIN
                        tlinenum := tlinenum + 1;

                        INSERT INTO ck_log
                                    (linenum, linemsg
                                    )
                             VALUES (tlinenum, 'NO MATCH FOUND'
                                    );

                        COMMIT;
                     END;
                  END IF;

                  t_error_found := 'Y';

                  SELECT DISTINCT table_name
                             INTO tt_dummychar
                             FROM user_cons_columns
                            WHERE owner = USER
                              AND constraint_name = t_r_constraint_name;

                  tlinenum := tlinenum + 1;

                  INSERT INTO ck_log
                              (linenum,
                               linemsg
                              )
                       VALUES (tlinenum,
                                  'Changing data in table '
                               || tt_dummychar
                               || ' will lock table '
                               || tt_table_name
                              );

                  COMMIT;
                  tlinenum := tlinenum + 1;

                  INSERT INTO ck_log
                              (linenum,
                               linemsg
                              )
                       VALUES (tlinenum,
                                  'Create an index on table '
                               || tt_table_name
                               || ' with the following columns to remove lock problem'
                              );

                  OPEN indexcolumns;

                  LOOP
                     FETCH indexcolumns
                      INTO err_table_name, err_column_name, err_position;

                     EXIT WHEN indexcolumns%NOTFOUND;
                     tlinenum := tlinenum + 1;

                     INSERT INTO ck_log
                                 (linenum,
                                  linemsg
                                 )
                          VALUES (tlinenum,
                                     'Column = '
                                  || err_column_name
                                  || ' ('
                                  || err_position
                                  || ')'
                                 );
                  END LOOP;

                  CLOSE indexcolumns;
            END;
         END LOOP;

         COMMIT;

         CLOSE concolumns;
      END LOOP;

      IF (t_error_found = 'N')
      THEN
         BEGIN
            tlinenum := tlinenum + 1;

            INSERT INTO ck_log
                        (linenum, linemsg
                        )
                 VALUES (tlinenum, 'No foreign key errors found'
                        );
         END;
      END IF;

      COMMIT;

      CLOSE tablecons;
   END LOOP;

   COMMIT;
END;
/

select LineMsg
from ck_log
where LineMsg NOT LIKE 'Checking%' AND
LineMsg NOT LIKE 'No foreign key%'
order by LineNum
/