Re: ORA-600 Deadlock Issues

  • From: Karthik <karthik@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 27 Jun 2005 17:44:06 +0530

Tom,

I used the following script to identify FK columns without indexes.

Thanks, Karthik


rem -----------------------------------------------------------------------
rem Shows the foreign keys without appropiate index
rem -----------------------------------------------------------------------
rem
SET echo off
SET verify off
--
COLUMN OWNER noprint new_value own
COLUMN TABLE_NAME format a24 wrap heading "Table Name"
COLUMN CONSTRAINT_NAME format a24 wrap heading "Constraint Name"
COLUMN CONSTRAINT_TYPE format a3 heading "Typ"
COLUMN COLUMN_NAME format a24 wrap heading "1. Column"
BREAK ON OWNER skip page
--
SET TERMOUT ON
TTITLE CENTER 'Unindexed Foreign Keys owned by Owner: ' own SKIP 2
PROMPT
PROMPT Please enter Owner Name and Table Name. Wildcards allowed (DEFAULT: %)
PROMPT
PROMPT eg.: SCOTT, S% OR %
PROMPT eg.: EMP, E% OR %
PROMPT
--
ACCEPT vOwner prompt "Owner <%>: " DEFAULT %
ACCEPT vTable prompt "Tables <%>: " DEFAULT %
--
SELECT OWNER, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM DBA_CONS_COLUMNS c
WHERE position=1 AND
(OWNER, TABLE_NAME, COLUMN_NAME) IN
(SELECT c.OWNER, c.TABLE_NAME,cc.COLUMN_NAME
FROM DBA_CONSTRAINTS c, DBA_CONS_COLUMNS cc
WHERE c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
AND c.TABLE_NAME = cc.TABLE_NAME
AND c.OWNER = cc.OWNER
AND c.CONSTRAINT_TYPE = 'R'
AND cc.POSITION = 1
AND c.OWNER LIKE UPPER('&vOwner')
AND c.TABLE_NAME LIKE UPPER('&vTable')
MINUS
SELECT table_owner, table_name, column_name
FROM DBA_IND_COLUMNS
WHERE COLUMN_POSITION = 1
AND TABLE_OWNER LIKE UPPER('&vOwner')
AND TABLE_NAME LIKE UPPER('&vTable')
)
ORDER BY OWNER, TABLE_NAME, CONSTRAINT_NAME;
--
ttitle off
SET pause off
COLUMN TABLE_NAME clear
COLUMN CONSTRAINT_NAME clear
COLUMN CONSTRAINT_TYPE clear
COLUMN COLUMN_NAME clear
clear breaks



On 27-Jun-05, at 5:18 PM, Mercadante, Thomas F (LABOR) wrote:

Karthik,

As you have figured out, the largest cause of Deadlock problems are bad
coding and missing indexes to support foreign keys.  Below is a sql to
help you find tables with foreign keys but no indexes to support them.
The query is not 100% bullet proof, but it is a start.

I use this for Curam applications as they do not believe in supplying
indexes for foreign keys (their official response is to drop the FK's -
nice, eh?).

Good Luck!

SELECT 'create index ' || substr(ut.table_name,1,20) ||
ROUND(sys.dbms_random.value*100)||'IDX ' ||
'ON ' || ut.table_name || ' (' || ucc.column_name || ') ' ||
'TABLESPACE ' || ut.TABLESPACE_NAME||'PK pctfree 10' ddl_string
FROM USER_TABLES ut, USER_CONSTRAINTS uc, USER_CONS_COLUMNS ucc
WHERE uc.constraint_type='R'
AND ucc.constraint_name = uc.constraint_name
AND ut.table_name = uc.table_name
AND NVL(position,1) = 1
AND NOT EXISTS(SELECT 1 FROM USER_IND_COLUMNS uic
WHERE uic.table_name=ucc.table_name
AND   ucc.column_name = uic.column_name
AND   uic.column_position=1)
ORDER BY 1;

Tom 

--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2005