whenever sqlerror exit;

set feedback on;
set newpage none;
set pagesize 0;
set tab off;

truncate table gmloadbadoids;
commit;
alter session set sort_area_size=67108864;

DECLARE
--Alles im Speicher halten, sonnst snapshot too old...
  TYPE TIdxTbl IS TABLE OF user_indexes.table_name%TYPE INDEX BY BINARY_INTEGER;
  TYPE TIdxIdx IS TABLE OF user_indexes.index_name%TYPE INDEX BY BINARY_INTEGER;
  idxTbl TIdxTbl;
  idxIdx TIdxIdx;

  TYPE TConTbl IS TABLE OF user_constraints.table_name%TYPE INDEX BY BINARY_INTEGER;
  TYPE TConCon IS TABLE OF user_constraints.constraint_name%TYPE INDEX BY BINARY_INTEGER;
  conTbl TConTbl;
  conCon TConCon;

  CURSOR C_exception IS select * from gmloadexceptions;

  oid CHAR(22);
  i   INTEGER;

BEGIN
--Zuerst alle unusable primary key constraints disabeln
  SELECT table_name, index_name BULK COLLECT INTO idxTbl, idxIdx FROM user_indexes where status<>'VALID';
  IF idxIdx.COUNT > 0 THEN
    FOR idx IN idxIdx.FIRST..idxIdx.LAST LOOP
      BEGIN
        EXECUTE IMMEDIATE 'alter table '||idxTbl(idx)||' disable constraint '||idxIdx(idx);
        COMMIT;
      EXCEPTION WHEN OTHERS THEN
--Wenn kein primary index, macht nichts
        i := 0;
      END;
    END LOOP;
    idxTbl.DELETE;
    idxIdx.DELETE;
  END IF;

--Alle disabelten constraints wieder enabeln
  SELECT table_name, constraint_name BULK COLLECT INTO conTbl, conCon FROM user_constraints where status<>'ENABLED';
  IF conCon.COUNT > 0 THEN
    FOR con IN conCon.FIRST..conCon.LAST LOOP
      EXECUTE IMMEDIATE 'truncate table gmloadexceptions';
      COMMIT;
      i := 0;
      BEGIN
        EXECUTE IMMEDIATE 'alter table '||conTbl(con)||' parallel enable constraint '||conCon(con)||' exceptions into gmloadexceptions';
      EXCEPTION WHEN OTHERS THEN
--Sonst oracle bug!
        COMMIT;
--Alle fehlerhaften Sätze rausschmeisen und loggen
        FOR R_exception IN C_exception LOOP
          EXECUTE IMMEDIATE 'select oid from '||R_exception.table_name||' where rowid=:1' INTO oid USING R_exception.row_id;
          EXECUTE IMMEDIATE 'insert into gmloadbadoids values (:1, :2)' USING R_exception.table_name, oid;
          EXECUTE IMMEDIATE 'delete from '||R_exception.table_name||' where rowid=:1' USING R_exception.row_id;
          i := i + 1;
          IF i >= 1000 THEN
            COMMIT;
            i := 0;
          END IF;
        END LOOP;
        COMMIT;
      END;
    END LOOP;
    conTbl.DELETE;
    conCon.DELETE;
  END IF;

--Indexe neu aufbauen
  SELECT index_name BULK COLLECT INTO idxIdx FROM user_indexes where status<>'VALID';
  IF idxIdx.COUNT > 0 THEN
    FOR idx IN idxIdx.FIRST..idxIdx.LAST LOOP
      EXECUTE IMMEDIATE 'alter index '||idxIdx(idx)||' rebuild parallel nologging';
      COMMIT;
    END LOOP;
    idxIdx.DELETE;
  END IF;

--Alle bearbeiteten constraints enabeln
  SELECT table_name, constraint_name BULK COLLECT INTO conTbl, conCon FROM user_constraints where status<>'ENABLED';
  IF conCon.COUNT > 0 THEN
    FOR con IN conCon.FIRST..conCon.LAST LOOP
      EXECUTE IMMEDIATE 'alter table '||conTbl(con)||' parallel enable constraint '||conCon(con);
      COMMIT;
    END LOOP;
    conTbl.DELETE;
    conCon.DELETE;
  END IF;

  COMMIT;
END;
/
select * from gmloadbadoids;