Script to Check for Foreign Key Locking Issues [ID 1019527.6]
Script. to Check for Foreign Key Locking Issues for a Specific User [ID 1019527.6]
Applies to:
Oracle Server - Personal Edition - Version 7.1.4.0 to 11.2.0.3 [Release 7.1.4 to 11.2]
Oracle Server - Standard Edition - Version 7.0.16.0 to 11.2.0.3 [Release 7.0 to 11.2]
Oracle Server - Enterprise Edition - Version 7.0.16.0 to 11.2.0.3 [Release 7.0 to 11.2]
Information in this document applies to any platform.
Oracle Server - Standard Edition - Version 7.0.16.0 to 11.2.0.3 [Release 7.0 to 11.2]
Oracle Server - Enterprise Edition - Version 7.0.16.0 to 11.2.0.3 [Release 7.0 to 11.2]
Information in this document applies to any platform.
Purpose
SCRIPT. TO CHECK FOR FOREIGN KEY LOCKING ISSUES
Requirements
Rdbms:07.0.X - 11.XX
Configuring
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.
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
Locking problems due to a FK column not being indexed, are discussed in the manual here:
Oracle® Database Concepts
11g Release 2 (11.2)
Part Number E25789-01
Chapter 9 Data Concurrency and Consistency
Locks and Foreign Keys:
http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#autoId24
Legacy information: 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.
Caution
This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.
Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.
Script
=======
Script.
=======
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 WNER = 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 WNER = USER
and CONSTRAINT_NAME = t_CONSTRAINT_NAME
order by POSITION;
cursor IndexColumns is
select TABLE_NAME,
COLUMN_NAME,
POSITION
from user_cons_columns
where WNER = 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 WNER = 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
/
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 WNER = 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 WNER = USER
and CONSTRAINT_NAME = t_CONSTRAINT_NAME
order by POSITION;
cursor IndexColumns is
select TABLE_NAME,
COLUMN_NAME,
POSITION
from user_cons_columns
where WNER = 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 WNER = 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
/
Sample Output
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)
--------------------------------------------------------------------------
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 Database Products > Oracle Database > Oracle Database > Oracle Server - Personal Edition > Generic RDBMS > Locking Issues
- Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Standard Edition > Generic RDBMS > Locking Issues
- Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition > RDBMS > Locking Issues
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-742175/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Script: To list Foreign Key Constraints (Doc ID 1039297.6)AI
- [Oracle Script] check userOracle
- [Oracle Script] check latchOracle
- check ftp success scriptFTP
- [Oracle Script] check active sessionOracleSession
- [Oracle Script] check lock infoOracle
- [Oracle Script] check Literal SQLOracleSQL
- script of check repair ASM DISKGROUPAIASM
- SQL FOREIGN KEY 約束SQL
- [Oracle Script] check tablespace usage infoOracle
- [Oracle Script] check temp tablespace usageOracle
- [Oracle Script] check object count by userOracleObject
- [Oracle Script] check File I/OOracle
- mysql 使用foreign key（外來鍵）MySql
- 關於primary key和foreign key的問題處理
- 【SQL】15 SQL 約束（Constraints）、NOT NULL 約束、UNIQUE 約束、PRIMARY KEY 約束、FOREIGN KEY 約束、CHECK 約束、DEFAULT約束SQLAINull
- foreign key的一些總結
- Flashback table with foreign key constraint.AI
- 詳解外來鍵約束（foreign key）
- Get your Windows product key from a scriptWindows
- Mysql 外來鍵(FOREIGN KEY)使用注意事項MySql
- REFERENTIAL INTEGRITY AND LOCKING [ID 33453.1]
- MySQL innodb 的間隙鎖定（next-key locking）MySql
- 外來鍵刪除(T-SQL Drop Foreign Key)SQL
- How to Troubleshoot Grid Infrastructure Startup IssuesASTStruct
- [轉] mysql 外來鍵（Foreign Key）的詳解和例項MySql
- imp 匯入遇到 FK (Foreign Key) 導致錯誤處理
- 檢測不再使用的索引–check-unused-keys索引
- 【MOS】Top 5 Grid Infrastructure Startup Issues (文件 ID 1368382.1)ASTStruct
- sql_mode...foreign_key_checks...unique_checks...sql_notes不能為nullSQLNull
- 【Foreign Key】Oracle外來鍵約束三種刪除行為Oracle
- Troubleshooting Database Hang Issues (Doc ID 1378583.1)Database
- IDC Script實戰
- How to Troubleshoot Grid Infrastructure Startup Issues [ID 1050908.1]ASTStruct
- Troubleshoot Grid Infrastructure Startup Issues (Doc ID 1050908.1)ASTStruct
- 聊聊Oracle外來鍵約束（Foreign Key）的幾個操作選項Oracle
- 建立外來鍵時報 Cannot add foreign key constraint 解決方法AI
- 如何使外來鍵(Foreign Key)或其他constraint失效的語句AI