1

Topic: deadlock at packet compilation

Oracle Database 11g Enterprise Edition Release 11.2.0.4.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?

2

Re: deadlock at packet compilation

I cannot make tea in a colander. I cover tea leaves, I flood boiled water, but water follows.
I tried to put tea in bags - does not help.
And tea in package at all .
How it is possible to solve this problem?

3

Re: deadlock at packet compilation

Pastic;
You chop off , ,  all invalids, you cut .

4

Re: deadlock at packet compilation

EDUARD_2;
And if the HARDWARE meant, what if to change the code in a packet  begin falls on ?

5

Re: deadlock at packet compilation

Arsenyev wrote:

And if the HARDWARE meant, what if to change the code in a packet  begin falls on ?

It would be already not attempt:

Pastic wrote:

at attempt to recompile

6

Re: deadlock at packet compilation

Elic;
Well if forced to return backwards and more so not to do, to it.

7

Re: deadlock at packet compilation

Well, the surgeon soldering on live cannot to be surprised that if it substitutes a packet that using it  during this moment .

8

Re: deadlock at packet compilation

And a construction

wrote:

insert into... select...

As

wrote:

attempt to recompile this packet (at the calculation of new changes)

Feeblly pulls

9

Re: deadlock at packet compilation

But my crystal full-sphere  also cracked all - I do not see obvious.

10

Re: deadlock at packet compilation

Arsenyev wrote:

And if the HARDWARE meant, what if to change the code in a packet  begin falls on ?

In what a difference? I will not understand? That the code in a packet exchanged, it is necessary it  that  - it is necessary to chop off all who uses this packet and other dependences. -  and applications.

11

Re: deadlock at packet compilation

Arsenyev wrote:

Well if forced to return backwards and more so not to do, to it.

Arsenyev wrote:

Well, cannot the surgeon soldering on live to be surprised that if it substitutes a packet that using it  during this moment .

the impression that you not in subject ORA-04021 Is added