How to Prevent Locks

2008-09-09T03:24:00.001-07:00

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.

IMPORTANT, PLEASE NOTE

This locking problem, due to a FK column not being indexed, is discussed
in the 8x/9x Concepts Manual under Data Integrity chapter.

Starting with Oracle9i, Release 1 (9.0.1), Oracle no longer requires
a share lock on unindexed foreign keys when doing an update or delete on
the primary key. It still obtains the table-level share lock, but then
releases it immediately after obtaining it. If multiple primary keys are
update or deleted, the lock is obtained and released once for each row.

References

FAQ about Detecting and Resolving Locking Conflicts

REFERENTIAL INTEGRITY AND LOCKING

Script

=======
Script:
=======

----------- cut ---------------------- cut -------------- cut --------------

SET ECHO off
REM NAME: TFSFKCHLK.SQL
REM USAGE:"@path/tfsfkchk"
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM None -- checks only the USER_ views
REM --------------------------------------------------------------------------
REM This file checks the current users Foreign Keys to make sure of the
REM following:
REM
REM 1) All the FK columns are have indexes to prevent a possible locking
REM problem that can slow down the database.
REM
REM 2) Checks the ORDER OF THE INDEXED COLUMNS. To prevent the locking
REM problem the columns MUST be index in the same order as the FK is
REM defined.
REM
REM 3) If the script finds and miss match the script reports the correct
REM order of columns that need to be added to prevent the locking
REM problem.
REM
REM
REM
REM -------------------------------------------------------------------------
REM Main text of script follows:

drop table ck_log;

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

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
/

----------- cut ---------------------- cut -------------- cut --------------

=========
Examples:
=========

LINEMSG

--------------------------------------------------------------------------
Changing
data in table DEPT will lock table EMP
Create an index on table EMP with the following columns to remove lock problem

Column = DEPTNO (1)

Changing data in table ITEM_CATEGORIES will lock table ITEMS
Create an index on table ITEMS with the following columns to remove lock problem

Column = ITEM_CAT (1)
Column = ITEM_BUS_UNIT (2)

Changing data in table EMP will lock table EMP
Create an index on table EMP with the following columns to remove lock problem

Column = MGR (1)

Changing data in table ORD will lock table CUSTOMER
Create an index on table CUSTOMER with the following columns to remove lock problem

Column = CUSTID (1)

Disclaimer

EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.

Limitation of Liability

IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.

Abstract

SCRIPT TO CHECK FOR FOREIGN KEY LOCKING ISSUES

Product Name, Product Version
Rdbms:07.0.X

Platform
Platform Independent

Date Created
07-NOV-2002

Instructions

Execution Environment:

Access Privileges:
If run as owner of objects no special priveleges required

Usage:
sqlplus / @[SCRIPTFILE]

Instructions:

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.

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.

IMPORTANT, PLEASE NOTE

The potential locking issues, due to a FK column not being indexed,
apply to versions prior to Oracle V7.1.5.

This locking problem is discussed in the Oracle 7 Server,
Application Developer's Guide Page 6-10 under the section
"On Index on the Foreign Key"

References

FAQ about Detecting and Resolving Locking Conflicts

REFERENTIAL INTEGRITY AND LOCKING

Script

=======
Script:
=======

----------- cut ---------------------- cut -------------- cut --------------

SET ECHO off
REM NAME: TFSFKCHLK.SQL
REM USAGE:"@path/tfsfkchk"
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM None -- checks only the USER_ views
REM --------------------------------------------------------------------------
REM PURPOSE:
REM This file checks the current users Foreign Keys to make sure of the
REM following:
REM
REM 1) All the FK columns are have indexes to prevent a possible locking
REM problem that can slow down the database.
REM
REM 2) Checks the ORDER OF THE INDEXED COLUMNS. To prevent the locking
REM problem the columns MUST be index in the same order as the FK is
REM defined.
REM
REM 3) If the script finds and miss match the script reports the correct
REM order of columns that need to be added to prevent the locking
REM problem.
REM
REM NOTES:
REM
REM - This locking problem is discussed in the
REM Oracle 7 Server, Application Developer's Guide Page 6-10
REM under the section "No Index on the Foreign Key"
REM
REM -----------------------------------------------------------------------
REM EXAMPLE:
REM LINEMSG
REM -------------------------------------------------------------------
REM Changing data in table ITEMS will lock table ITEM_CATEGORIES
REM Create an index on the following columns to remove lock problem
REM
REM Column = ITEM_CAT (1)
REM Column = ITEM_BUS_UNIT (2)
REM
REM Changing data in table ITEMS will lock table ITEM_CATEGORIES
REM Create an index on the following columns to remove lock problem
REM
REM Column = ITEM_CAT (1)
REM Column = ITEM_BUS_UNIT (2)
REM -----------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM -------------------------------------------------------------------------
REM Main text of script follows:

drop table ck_log;

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

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
-- where Table_Name = 'WORKERS_COMP_CLAIMS'
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_TABLE_NAME
||' will lock table ' ||tt_dummyChar);

commit;
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum,'Create an index on 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 Probl%'
order by LineNum
/

----------- cut ---------------------- cut -------------- cut --------------

=========
Examples:
=========

LINEMSG

--------------------------------------------------------------------------
Changing
data in table EMP will lock table DEPT
Create an index on the following columns to remove lock
problem

Column
= DEPTNO (1)

Changing data in table EMP will lock table EMP
Create an index on the following columns to remove lock
problem

Column = MGR (1)

Changing data in table ITEMS will lock table ITEM_CATEGORIES
Create an index on the following columns to remove lock
problem

Column = ITEM_CAT (1)
Column = ITEM_BUS_UNIT (2)

Changing data in table ITEMS will lock table ITEM_CATEGORIES
Create an index on the following columns to remove lock
problem

Column = ITEM_CAT (1)
Column = ITEM_BUS_UNIT (2)

Changing data in table CUSTOMER will lock table ORD
Create an index on the following columns to remove lock
problem

Column = CUSTID (1)

Disclaimer

EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.

Limitation of Liability

IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.

Abstract

SCRIPT TO CHECK FOR FOREIGN KEY LOCKING ISSUES

Product Name, Product Version
Rdbms:07.0.X - 08.XX

Platform
Platform Independent

Date Created
07-NOV-2002

Instructions

Execution Environment:

Access Privileges:
If run as owner of objects no special priveleges required

Usage:
sqlplus / @[SCRIPTFILE]

Instructions:

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.

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.

IMPORTANT, PLEASE NOTE

This locking problem, due to a FK column not being indexed, is discussed
in the 8x/9x Concepts Manual under Data Integrity chapter.

Starting with Oracle9i, Release 1 (9.0.1), Oracle no longer requires
a share lock on unindexed foreign keys when doing an update or delete on
the primary key. It still obtains the table-level share lock, but then
releases it immediately after obtaining it. If multiple primary keys are
update or deleted, the lock is obtained and released once for each row.

References

FAQ about Detecting and Resolving Locking Conflicts

REFERENTIAL INTEGRITY AND LOCKING

Script

=======
Script:
=======

----------- cut ---------------------- cut -------------- cut --------------

SET ECHO off
REM NAME: TFSFKCHLK.SQL
REM USAGE:"@path/tfsfkchk"
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM None -- checks only the USER_ views
REM --------------------------------------------------------------------------
REM This file checks the current users Foreign Keys to make sure of the
REM following:
REM
REM 1) All the FK columns are have indexes to prevent a possible locking
REM problem that can slow down the database.
REM
REM 2) Checks the ORDER OF THE INDEXED COLUMNS. To prevent the locking
REM problem the columns MUST be index in the same order as the FK is
REM defined.
REM
REM 3) If the script finds and miss match the script reports the correct
REM order of columns that need to be added to prevent the locking
REM problem.
REM
REM NOTES:
REM
REM This locking problem, due to a FK column not being indexed, is discussed
REM in the 8x/9x Concepts Manual under Data Integrity chapter.
REM
REM Starting with Oracle9i, Release 1 (9.0.1), Oracle no longer requires
REM a share lock on unindexed foreign keys when doing an update or delete on
REM the primary key. It still obtains the table-level share lock, but then
REM releases it immediately after obtaining it. If multiple primary keys are
REM update or deleted, the lock is obtained and released once for each row.
REM
REM -----------------------------------------------------------------------
REM EXAMPLE:
REM
REM LINEMSG
REM -------------------------------------------------------------------
REM Changing data in table DEPT will lock table EMP
REM Create an index on table EMP with the following columns to remove lock problem
REM
REM Column = DEPTNO (1)
REM
REM Changing data in table ITEM_CATEGORIES will lock table ITEMS
REM Create an index on table ITEMS with the following columns to remove lock problem
REM
REM Column = ITEM_CAT (1)
REM Column = ITEM_BUS_UNIT (2)
REM
REM -------------------------------------------------------------------------
REM Main text of script follows:

drop table ck_log;

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

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
/

----------- cut ---------------------- cut -------------- cut --------------

=========
Examples:
=========

LINEMSG

--------------------------------------------------------------------------
Changing
data in table DEPT will lock table EMP
Create an index on table EMP with the following columns to remove lock problem

Column = DEPTNO (1)

Changing data in table ITEM_CATEGORIES will lock table ITEMS
Create an index on table ITEMS with the following columns to remove lock problem

Column = ITEM_CAT (1)
Column = ITEM_BUS_UNIT (2)

Changing data in table EMP will lock table EMP
Create an index on table EMP with the following columns to remove lock problem

Column = MGR (1)

Changing data in table ORD will lock table CUSTOMER
Create an index on table CUSTOMER with the following columns to remove lock problem

Column = CUSTID (1)

Disclaimer

EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.

Limitation of Liability

IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.

Abstract

SCRIPT TO CHECK FOR FOREIGN KEY LOCKING ISSUES

Product Name, Product Version
Rdbms:07.0.X

Platform
Platform Independent

Date Created
07-NOV-2002

Instructions

Execution Environment:

Access Privileges:
If run as owner of objects no special priveleges required

Usage:
sqlplus / @[SCRIPTFILE]

Instructions:

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.

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.

IMPORTANT, PLEASE NOTE

The potential locking issues, due to a FK column not being indexed,
apply to versions prior to Oracle V7.1.5.

This locking problem is discussed in the Oracle 7 Server,
Application Developer's Guide Page 6-10 under the section
"On Index on the Foreign Key"

References

FAQ about Detecting and Resolving Locking Conflicts

REFERENTIAL INTEGRITY AND LOCKING

Script

=======
Script:
=======

----------- cut ---------------------- cut -------------- cut --------------

SET ECHO off
REM NAME: TFSFKCHLK.SQL
REM USAGE:"@path/tfsfkchk"
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM None -- checks only the USER_ views
REM --------------------------------------------------------------------------
REM PURPOSE:
REM This file checks the current users Foreign Keys to make sure of the
REM following:
REM
REM 1) All the FK columns are have indexes to prevent a possible locking
REM problem that can slow down the database.
REM
REM 2) Checks the ORDER OF THE INDEXED COLUMNS. To prevent the locking
REM problem the columns MUST be index in the same order as the FK is
REM defined.
REM
REM 3) If the script finds and miss match the script reports the correct
REM order of columns that need to be added to prevent the locking
REM problem.
REM
REM NOTES:
REM
REM - This locking problem is discussed in the
REM Oracle 7 Server, Application Developer's Guide Page 6-10
REM under the section "No Index on the Foreign Key"
REM
REM -----------------------------------------------------------------------
REM EXAMPLE:
REM LINEMSG
REM -------------------------------------------------------------------
REM Changing data in table ITEMS will lock table ITEM_CATEGORIES
REM Create an index on the following columns to remove lock problem
REM
REM Column = ITEM_CAT (1)
REM Column = ITEM_BUS_UNIT (2)
REM
REM Changing data in table ITEMS will lock table ITEM_CATEGORIES
REM Create an index on the following columns to remove lock problem
REM
REM Column = ITEM_CAT (1)
REM Column = ITEM_BUS_UNIT (2)
REM -----------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM -------------------------------------------------------------------------
REM Main text of script follows:

drop table ck_log;

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

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
-- where Table_Name = 'WORKERS_COMP_CLAIMS'
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_TABLE_NAME
||' will lock table ' ||tt_dummyChar);

commit;
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum,'Create an index on 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 Probl%'
order by LineNum
/

----------- cut ---------------------- cut -------------- cut --------------

=========
Examples:
=========

LINEMSG

--------------------------------------------------------------------------
Changing
data in table EMP will lock table DEPT
Create an index on the following columns to remove lock
problem

Column
= DEPTNO (1)

Changing data in table EMP will lock table EMP
Create an index on the following columns to remove lock
problem

Column = MGR (1)

Changing data in table ITEMS will lock table ITEM_CATEGORIES
Create an index on the following columns to remove lock
problem

Column = ITEM_CAT (1)
Column = ITEM_BUS_UNIT (2)

Changing data in table ITEMS will lock table ITEM_CATEGORIES
Create an index on the following columns to remove lock
problem

Column = ITEM_CAT (1)
Column = ITEM_BUS_UNIT (2)

Changing data in table CUSTOMER will lock table ORD
Create an index on the following columns to remove lock
problem

Column = CUSTID (1)

Oracle Guru

How to Prevent Locks