oracle 11.2.0.3 StdEd
Oracle Linux 5.6 x64
I think I already know the answer is negative, but in the interest of “you never know what you don’t know” . . .
Yesterday we had a job fail with
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
The failing statement was
execute immediate 'ALTER INDEX "xx"."xxxxxxx" UNUSABLE';
ddl_lock_timeout is at default zero, so the failure is immediate.
I’m thinking that with an immediate failure, by the time you learn of the failure, there are no residual pieces of evidence in the v$ views to allow you to find out who was holding the lock that caused the failure. Am I wrong, and don’t know it?
I know we can set ddl_lock_timeout at either the system or session level, and if it were set to a sufficiently long period we might be able to capture the offender, but am not sure that is an acceptable solution. It would have to be discussed and evaluated internally.