- Had a 'NOWAIT' clause so instead of blocking the statement returns this error.
- Was DDL and was blocked.
- None normally required unless this is occurring when not expected.
- Check v$lock for locks being held.
- For DDL repeat the command. If the DDL is issued under DBMS_SQL it is possible to cause this error if the DDL touches an object related to the current PL/SQL block by some dependency.
- For SELECT FOR UPDATE issue the same statement without the NOWAIT clause to see if the statement blocks (assuming the problem is reproducible). If it blocks check there is a blocker in v$lock to find the blocked process.
- V$LOCKED_OBJECT: This view lists all locks acquired by every transaction on the system.
s.paddr = p.addr
s.sid in (select SESSION_ID from v$locked_object);
- Re-execute the DDL after some time when the when the objects gets released from the lock.
- If the task needs to be completed immediately, kill the sessions that are preventing the exclusive lock.
- If it is bulk job which takes long time frame, get a maintenance window to prevent end user to connect to the database and then run the DDL.