oracle - specified - resolver ora 00054

Oracle DDL en transacción autónoma

Question

Necesito ejecutar un montón de (hasta ~ 1000000) sentencias SQL en una base de datos Oracle. Estas declaraciones deberían dar como resultado un estado referencialmente consistente al final, y todas las declaraciones deberían revertirse si ocurre un error. Estas declaraciones no vienen en un orden referencial. Por lo tanto, si las restricciones de clave externa están habilitadas, una de las sentencias puede causar una violación de clave externa, aunque esta violación se solucionará con una sentencia que se ejecutará más adelante.

Intenté deshabilitar las claves foráneas primero y habilitarlas después de que se ejecutaran todas las sentencias. Pensé que podría retroceder cuando hubiera una violación real de clave externa. Aunque me equivoqué, descubrí que cada declaración DDL en Oracle comenzaba con una confirmación, por lo que no había forma de deshacer las declaraciones de esta manera. Aquí está mi script para deshabilitar claves foráneas:

begin 
  for i in (select constraint_name, table_name from user_constraints
            where constraint_type =''R'' and status = ''ENABLED'') 
    LOOP execute immediate ''alter table ''||i.table_name||'' disable constraint 
                           ''||i.constraint_name||''''; 
  end loop;
end;

Después de algunas investigaciones, descubrí que se recomendaba ejecutar sentencias DDL, como en este caso, en una transacción autónoma. Así que traté de ejecutar sentencias DDL en una transacción autónoma. Esto dio como resultado el siguiente error:

ORA-00054: recurso ocupado y adquirir con NOWAIT especificado

Supongo que esto se debe a que la transacción principal aún tiene bloqueo DDL en las tablas.

¿Estoy haciendo algo mal aquí, o hay alguna otra forma de hacer que este escenario funcione?

Answer 1

Hay varios enfoques posibles.

Lo primero a tener en cuenta es que haga lo que haga en el nivel de la tabla se aplicará a todas las sesiones que utilicen esa tabla. Si no tiene acceso exclusivo a esa tabla, probablemente no desee eliminar / recrear restricciones, ni desactivarlas / habilitarlas.

Lo segundo a tener en cuenta es que probablemente no quiera estar en la posición de deshacer un millón de insertos / actualizaciones. El retroceso puede ser LENTO.

En general, me gustaría cargar en una tabla temporal. Luego, haga un único INSERT de la tabla temporal en la tabla de destino. Como una declaración única, Oracle aplicará todas las restricciones de verificación al final.

Si no puede pasar por una tabla temporal (por ejemplo, actualizaciones de datos existentes), antes de comenzar, haga que las restricciones diferibles inicialmente sean inmediatas . Luego, dentro de tu sesión,

SET CONSTRAINTS emp_job_nn, emp_salary_min DEFERRED;

A continuación, puede aplicar los cambios y, cuando se comprometa, las restricciones se validarán.

Debería personalizarse con el registro de errores DML, ya que puede ayudar a identificar las filas que causan violaciones.