I have a procedure in pl/sql which works fine with small amount of data. The issue is, when is a large amount of data, cursor6 sometimes hangs session and other users can't access table5. table5 has a primary key column "ocf" which is the user id, so each user access only rows coresponding his user id. Even under these conditions, the cursor6 locks the table5 and create a dead session and, I don't understand how, but it doesn't allow other users to access it even if they use other rows in table5. Is there a solution to recreate that cursor for update or use something that may do the same thing, avoiding in this way that hanging session?
cursor cursor6(pf varchar2,PO number, psu varchar2) is select * from table5 where ocf=PO and .. for update;
row1 cursor6%rowtype;
BEGIN
delete table5 where ocf=PO;
commit;
open cursor1;
fetch cursor1 into wv,wd;
if cursor1%found then
open cursor2;
fetch cursor2 into wf;
if cursor2%found then
while cursor2%found loop
open cursor3;
fetch cursor3 into wco;
if cursor3%found then
while cursor3%found loop
open cursor5;
fetch cursor5 into ws;
while cursor5%found loop
open cursor4;
fetch cursor4 into ..;
if cursor4%found then
open cursor6(..);
fetch cursor6 into row1;
if cursor6%notfound then insert into table5 values (..);
else update table5 set ... where current of cursor6;
end if;
close cursor6;
end if;
close cursor4;
end loop;
close cursor5;
end loop;
end if;
close cursor3;
end loop;
end if;
close cursor2;
end if;
close cursor1;
commit;
delete table5 where ocf=PO;
commit;
Gosh, good luck with 6 nested loops.
cursor6 locks the table5 and create a dead session and, I don't understand how, but it doesn't allow other users to acces
That happens when foreign key columns aren't indexed. I suggest you check whether that's the case and - if so - create indexes.
Here's a query (author is Tom Kyte) (I don't have a link to the original, sorry; Google for it if you want) which displays unindexed foreign key constraints. I modified it a little bit by adding what to display:
PAR_WHAT:
0 - show only missing ones1 - show valid onesNULL - show allI suggest you first run it with PAR_WHAT = 0.
WITH forkey
AS (SELECT DECODE (b.table_name, NULL, '****', 'ok') Status,
a.table_name,
a.columns column_1,
b.columns column_2
FROM ( SELECT SUBSTR (a.table_name, 1, 30) table_name,
SUBSTR (a.constraint_name, 1, 30) constraint_name,
MAX (
DECODE (position,
1, SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (position,
2, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (position,
3, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (position,
4, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (position,
5, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (position,
6, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (position,
7, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (position,
8, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (position,
9, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (
position,
10, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (
position,
11, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (
position,
12, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (
position,
13, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (
position,
14, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (
position,
15, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (
position,
16, ', ' || SUBSTR (column_name, 1, 30),
NULL))
columns
FROM user_cons_columns a, user_constraints b
WHERE a.constraint_name = b.constraint_name
AND b.constraint_type = 'R'
GROUP BY SUBSTR (a.table_name, 1, 30),
SUBSTR (a.constraint_name, 1, 30)) a,
( SELECT SUBSTR (table_name, 1, 30) table_name,
SUBSTR (index_name, 1, 30) index_name,
MAX (
DECODE (column_position,
1, SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (column_position,
2, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (column_position,
3, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (column_position,
4, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (column_position,
5, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (column_position,
6, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (column_position,
7, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (column_position,
8, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (column_position,
9, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (
column_position,
10, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (
column_position,
11, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (
column_position,
12, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (
column_position,
13, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (
column_position,
14, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (
column_position,
15, ', ' || SUBSTR (column_name, 1, 30),
NULL))
|| MAX (
DECODE (
column_position,
16, ', ' || SUBSTR (column_name, 1, 30),
NULL))
columns
FROM user_ind_columns
GROUP BY SUBSTR (table_name, 1, 30),
SUBSTR (index_name, 1, 30)) b
WHERE a.table_name = b.table_name(+)
AND b.columns(+) LIKE a.columns || '%'
AND a.table_name NOT LIKE 'HEP_DP%')
SELECT f.status,
f.table_name,
f.column_1,
f.column_2
FROM forkey f
WHERE f.status =
CASE
WHEN :par_what = 1 THEN 'ok'
WHEN :par_what = 0 THEN '****'
ELSE f.status
END
ORDER BY f.table_name, f.column_1, f.column_2;