Отключение триггеров и констрейнтов

Делал это для подгрузки данных в Oracle из дампов. Может кому пригодится.

Сначала создаем 2 таблицы:

create table tmp_dbobj 
  (name varchar2(50), 
   otype varchar2(50));
   
create table tmp_cons_log 
  (cname varchar2(50), 
   ctype varchar2(1), 
   tname varchar2(50), 
   err_text varchar2(2000), 
   cols varchar2(1000), 
   vals varchar2(1000));

Отключаем:

--Выключение констрейнтов
declare
  vDDL varchar2(2000);
begin
  --triggers
  for c_trg in (select * from user_triggers where status = 'ENABLED') loop
    vDDL := 'alter trigger '||c_trg.trigger_name||' disable';
    execute immediate vDDL;
    insert into tmp_dbobj (name, otype)
      values (c_trg.trigger_name, 'trigger');
    commit;
  end loop;
  --FK
  for c_cons in (select * from user_constraints 
                 where constraint_type in ('R') and status = 'ENABLED'
                   --and constraint_name not like 'SYS%'
                 ) loop
    vDDL := 'alter table '||c_cons.table_name||' disable constraint '||c_cons.constraint_name;
    execute immediate vDDL;
    insert into tmp_dbobj (name, otype)
      values (c_cons.constraint_name, 'constraint');
    commit;
  end loop;
  --UK
  for c_cons in (select * from user_constraints 
                 where constraint_type in ('U') and status = 'ENABLED' 
                 ) loop
    vDDL := 'alter table '||c_cons.table_name||' disable constraint '||c_cons.constraint_name;
    execute immediate vDDL;
    insert into tmp_dbobj (name, otype)
      values (c_cons.constraint_name, 'constraint');
    commit;
  end loop;
end;

Данные об отключенных объектах сохраняются в таблицу tmp_dbobj.

Включаем, при этом, если констрейнт не включается происходит проверка, результаты которой (записи на которых констрейнт падает) сохраняются в tmp_cons_log (ВНИМАНИЕ! Тут используется функция – строковый агрегатор, отсутствующая в Oracle):

--проверка и включение констрейнтов
declare
  vSQL varchar2(1000);
  vDDL varchar2(2000);
  vCount number;
begin
  --Уникальные констрайнты
  for c1 in (select a.* 
               from tmp_dbobj a,
                    user_constraints b
               where a.otype = 'constraint'
                 and b.constraint_name = a.name 
                 and b.constraint_type = 'U') loop
    for c_cols in (select constraint_name, table_name, str_agg(column_name) cols, str_agg(column_name||' is null') null_cols
                     from user_cons_columns 
                     where constraint_name = c1.name
                     group by constraint_name, table_name) loop
      c_cols.null_cols := replace(c_cols.null_cols,',',' and ');
      vSQL := 'select 1 from dual where not exists (select '||c_cols.cols||', count(*) cnt from '||
        c_cols.table_name||
        ' where not ('||c_cols.null_cols||') group by '||c_cols.cols||' having count(*)>1 )';
      begin
        execute immediate vSQL into vCount;
        vDDL := 'alter table '||c_cols.table_name||' enable constraint '||c1.name;
        execute immediate vDDL;
        delete from tmp_dbobj where name = c1.name;
        commit;
      exception
        when no_data_found then
          vSQL := 'begin '||
                  'for c1 in ( select '||replace(c_cols.cols,',','||'',''||')||' vals from ('||
                  'select '||c_cols.cols||', count(*) cnt from '||c_cols.table_name||
                  'where not ('||c_cols.null_cols||') group by '||c_cols.cols||' having count(*)>1 )) loop'||
                  'insert into tmp_cons_log (cname, ctype, tname, err_text, cols, vals)'||
                  'values ('''||c_cols.constraint_name||''', ''U'', '''||c_cols.table_name||''', ''Дублирующие значения'', '''||
                  c_cols.cols||''', c1.vals); '||
                  'end loop; commit; end;';
          execute immediate vSQL;
      end;
    end loop;
  end loop;
  --Все остальные (FK)
  for c1 in (select a.*, b.table_name
               from tmp_dbobj a,
                    user_constraints b
               where a.otype = 'constraint'
                 and b.constraint_name = a.name 
                 and b.constraint_type = 'R') loop
    begin
      vDDL := 'alter table '||c1.table_name||' enable constraint '||c1.name;
      execute immediate vDDL;
      delete from tmp_dbobj where name = c1.name;
      commit;
    exception
      when others then
        for c_cols in (select col.constraint_name, col.table_name, r_col.table_name r_table_name,
                              fdc_str_agg(col.column_name) cols, 
                              fdc_str_agg('r.'||r_col.column_name||' = a.'||col.column_name) w_cols,
                              fdc_str_agg('a.'||col.column_name||' is not null') w_cols2
                         from user_constraints con,
                              user_cons_columns col,
                              user_cons_columns r_col
                         where con.constraint_name = c1.name
                           and col.constraint_name = con.constraint_name
                           and r_col.constraint_name = con.r_constraint_name
                           and col.position = r_col.position
                         group by col.constraint_name, col.table_name, r_col.table_name) loop
          vSQL := 'begin '||
                  'for c2 in ( select '||replace(c_cols.cols,',','||'',''||')||' vals from '||
                  c_cols.table_name||' a '||
                  'where not exists (select 1 from '||c_cols.r_table_name||' r '||
                  'where '||replace(c_cols.w_cols,',',' and ')||')'||
                  'and '||replace(c_cols.w_cols2,',',' and ')||
                  ') loop '||
                  'insert into tmp_cons_log (cname, ctype, tname, err_text, cols, vals)'||
                  'values ('''||c_cols.constraint_name||''', ''R'', '''||c_cols.table_name||''', ''Нет родительской записи'', '''||
                  c_cols.cols||''', c2.vals); '||
                  'end loop; commit; end;';
          dbms_output.put_line(vSQL);
          execute immediate vSQL;
        end loop;
    end;
  end loop;
  --триггера
  for c1 in (select * from tmp_dbobj where otype = 'trigger') loop
    vDDL := 'alter trigger '||c1.name||' enable';
    execute immediate vDDL;
    delete from tmp_dbobj where name = c1.name;
    commit;
  end loop;  
end;