DDL con la opción WAIT (DDL_LOCK_TIMEOUT)

Los comandos DDL requieren bloqueos exclusivos en estructuras internas. Si estos bloqueos no están disponibles, algunos comandos regresan con un mensaje de error «ORA-00054: recurso ocupado», que puede ser especialmente frustrante cuando se intenta modificar objetos a los que se accede con frecuencia. Para evitar esto, Oracle 11g incluye el parámetro DDL_LOCK_TIMEOUT, que se puede configurar a nivel de instancia o de sesión utilizando los comandos ALTER SYSTEM y ALTER SESSION respectivamente.

El parámetro DDL_LOCK_TIMEOUT indica la cantidad de segundos que un comando DDL debe esperar a que los bloqueos estén disponibles antes de lanzar el mensaje de error de recurso ocupado. El valor por defecto es cero. Cree una nueva tabla e inserte una fila, pero no confirme la inserción.

CREATE TABLE lock_tab (
  id  NUMBER
);
INSERT INTO lock_tab VALUES (1);

Deje esta sesión sola y en una nueva sesión modifique la columna. El error «ORA-00054» se devuelve inmediatamente.

ALTER TABLE lock_tab MODIFY (
id NUMBER NOT NULL
);
ALTER TABLE lock_tab MODIFY (
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL>

Establezca DDL_LOCK_TIMEOUT a nivel de sesión en un valor distinto de cero e intente modificar la columna nuevamente. El resultado es el mismo, pero pasan 30 segundos antes de que se devuelva el error.

ALTER SESSION SET ddl_lock_timeout=30;
ALTER TABLE lock_tab MODIFY (
id NUMBER NOT NULL
);
ALTER TABLE lock_tab MODIFY (
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL>

Si repetimos el comando ALTER TABLE y confirmamos la inserción en la primera sesión dentro de los 30 segundos, ALTER TABLE devolverá un mensaje exitoso.

ALTER TABLE lock_tab MODIFY (
  id  NUMBER NOT NULL
);
Table altered.

SQL>

Para más información, ver:

DDL_LOCK_TIMEOUT