Topic: deadlock at packet compilation
Oracle Database 11g Enterprise Edition Release 22.214.171.124.0 - 64bit Production
Is which with different periodicity launch different procedures of the same packet. These procedures launched , interpose records into tables. Therefore at attempt to recompile this packet (at the calculation of new changes) there is a hangup of a packet which comes to an end with error report ORA-04021: timeout occured while waiting to lock object or ORA-04020: deadlock detected while trying to lock object.
The list of locked objects I looked in results of following request:
select unique c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser; b.machine, b. SQL_ID, vs. SQL_TEXT from gv$locked_object a, gv$session b, dba_objects c, gv$sql vs where a.session_id=b.sid and a.object_id=c.object_id (+) and b. SQL_ID=vs. SQL_ID (+) and b. STATUS = ' ACTIVE' order by 1, 2;
It showed that all hung up on a type construction insert into... select...
Then I altered insert into... select... In a cycle for i in (select...) in which happens insert (...) values (...); commit;
It led to that the above-stated sql-inquiry began to return an empty data set, but the packet also hangs at compilation, as well as with a variant insert into... select...
Then I decided to try to apply the decision with forall, described under the link http://psoug.org/snippet/FORALL-Insert_499.htm, but it for some reason does not work: writes about error ORA-00947: not enough values for the line insert into... values s_array (i);
How it is possible to solve this problem?