Deshabilitar todas las restricciones de tabla en Oracle

¿Cómo puedo desactivar todas las restricciones de tabla en Oracle con un solo command? Esto puede ser para una sola tabla, una list de tablas o para todas las tablas.

Es mejor evitar escribir files de spool temporales. Use un bloque PL / SQL. Puede ejecutar esto desde SQL * Plus o poner esto en un package o procedimiento. La unión a USER_TABLES está ahí para evitar ver restricciones.

Es poco probable que realmente desee deshabilitar todas las restricciones (incluidas NOT NULL, keys principales, etc.). Debería pensar en poner constraint_type en la cláusula WHERE.

BEGIN FOR c IN (SELECT c.owner, c.table_name, c.constraint_name FROM user_constraints c, user_tables t WHERE c.table_name = t.table_name AND c.status = 'ENABLED' AND NOT (t.iot_type IS NOT NULL AND c.constraint_type = 'P') ORDER BY c.constraint_type DESC) LOOP dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name); END LOOP; END; / 

Habilitar las restricciones nuevamente es un poco complicado: debe habilitar las restricciones de key primaria antes de poder hacer reference a ellas en una restricción de key externa. Esto se puede hacer usando un ORDER BY en constraint_type. 'P' = key principal, 'R' = key externa.

 BEGIN FOR c IN (SELECT c.owner, c.table_name, c.constraint_name FROM user_constraints c, user_tables t WHERE c.table_name = t.table_name AND c.status = 'DISABLED' ORDER BY c.constraint_type) LOOP dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name); END LOOP; END; / 

Para tener en count las dependencies entre las restricciones:

 SET Serveroutput ON BEGIN FOR c IN (SELECT c.owner,c.table_name,c.constraint_name FROM user_constraints c,user_tables t WHERE c.table_name=t.table_name AND c.status='ENABLED' ORDER BY c.constraint_type DESC,c.last_change DESC ) LOOP FOR D IN (SELECT P.Table_Name Parent_Table,C1.Table_Name Child_Table,C1.Owner,P.Constraint_Name Parent_Constraint, c1.constraint_name Child_Constraint FROM user_constraints p JOIN user_constraints c1 ON(p.constraint_name=c1.r_constraint_name) WHERE(p.constraint_type='P' OR p.constraint_type='U') AND c1.constraint_type='R' AND p.table_name=UPPER(c.table_name) ) LOOP dbms_output.put_line('. Disable the constraint ' || d.Child_Constraint ||' (on table '||d.owner || '.' || d.Child_Table || ')') ; dbms_utility.exec_ddl_statement('alter table ' || d.owner || '.' ||d.Child_Table || ' disable constraint ' || d.Child_Constraint) ; END LOOP; END LOOP; END; / 

No es un solo command, pero así es como lo hago. La siguiente secuencia de commands se diseñó para ejecutarse en SQL * Plus. Tenga en count que, intencionalmente, he escrito esto para que solo funcione dentro del esquema actual.

 set heading off spool drop_constraints.out select 'alter table ' || owner || '.' || table_name || ' disable constraint ' || -- or 'drop' if you want to permanently remove constraint_name || ';' from user_constraints; spool off set heading on @drop_constraints.out 

Para restringir lo que suelta, filtre agregue una cláusula where a la instrucción select:

  • filtrar por constraint_type para descartar solo types particulares de restricciones
  • filtrar en table_name para hacerlo solo para una o algunas tablas.

Para ejecutar más que el esquema actual, modifique la instrucción select para seleccionar from all_constraints en lugar de user_constraints.

Nota : por alguna razón, no puedo hacer que el guión bajo NO actúe como una cursiva en el párrafo anterior. Si alguien sabe cómo solucionarlo, no dude en editar esta respuesta.

Esto se puede crear con secuencias de commands en PL / SQL basándonos simplemente en la vista del sistema DBA / ALL / USER_CONSTRAINTS, pero varios detalles no son tan triviales como parece. Debe tener cuidado con el order en que se realiza y también debe tener en count la presencia de índices únicos.

El order es importante porque no puede soltar una key única o primaria a la que hace reference una key externa, y puede haber keys externas en las tablas de otros esquemas que hacen reference a keys primarias propias, de modo que a less que tenga el privilegio ALTER ANY TABLE, entonces no puede soltar esos PKs y UKs. Además, no puede cambiar un índice único a un índice no exclusivo, por lo que debe soltarlo para eliminar la restricción (por esta razón, casi siempre es mejor implementar restricciones únicas como una restricción "real" que es compatible con un no -un índice único).

Use el cursor siguiente para deshabilitar todas las restricciones. Y modifique la consulta para habilitar las restricciones …

 DECLARE cursor r1 is select * from user_constraints; cursor r2 is select * from user_tables; BEGIN FOR c1 IN r1 loop for c2 in r2 loop if c1.table_name = c2.table_name and c1.status = 'ENABLED' THEN dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' disable constraint ' || c1.constraint_name); end if; end loop; END LOOP; END; / 

No parece que puedas hacer esto con un solo command, pero aquí está lo más cercano que pude encontrar.

Esta es otra forma de deshabilitar restricciones (proviene de https://asktom.oracle.com/pls/asktom/f?p=100:11:2402577774283132::::P11_QUESTION_ID:399218963817 )

 WITH qry0 AS (SELECT 'ALTER TABLE ' || child_tname || ' DISABLE CONSTRAINT ' || child_cons_name disable_fk , 'ALTER TABLE ' || parent_tname || ' DISABLE CONSTRAINT ' || parent.parent_cons_name disable_pk FROM (SELECT a.table_name child_tname ,a.constraint_name child_cons_name ,b.r_constraint_name parent_cons_name ,LISTAGG ( column_name, ',') WITHIN GROUP (ORDER BY position) child_columns FROM user_cons_columns a ,user_constraints b WHERE a.constraint_name = b.constraint_name AND b.constraint_type = 'R' GROUP BY a.table_name, a.constraint_name ,b.r_constraint_name) child ,(SELECT a.constraint_name parent_cons_name ,a.table_name parent_tname ,LISTAGG ( column_name, ',') WITHIN GROUP (ORDER BY position) parent_columns FROM user_cons_columns a ,user_constraints b WHERE a.constraint_name = b.constraint_name AND b.constraint_type IN ('P', 'U') GROUP BY a.table_name, a.constraint_name) parent WHERE child.parent_cons_name = parent.parent_cons_name AND (parent.parent_tname LIKE 'V2_%' OR child.child_tname LIKE 'V2_%')) SELECT DISTINCT disable_pk FROM qry0 UNION SELECT DISTINCT disable_fk FROM qry0; 

Funciona de maravilla

En el script "disable", la cláusula order by debería ser la siguiente:

 ORDER BY c.constraint_type DESC, c.last_change DESC 

El objective de esta cláusula es desactivar las restricciones en el order correcto.

 SELECT 'ALTER TABLE '||substr(c.table_name,1,35)|| ' DISABLE CONSTRAINT '||constraint_name||' ;' FROM user_constraints c, user_tables u WHERE c.table_name = u.table_name; 

Esta statement devuelve los commands que desactivan todas las restricciones, incluida la key principal, las keys externas y otras restricciones.