Löschen in Blöcken

Wenn umfangreiche Datenmengen aus einer Tabelle gelöscht werden sollen, gibt es verschiedene Ansätze. Kann alles weg, ist ein TRUNCATE TABLE ... der entspannteste Weg, sofern man sich nicht mit Abhängigkeiten herumschlagen muss. Müssen aber mal viele Datensätze gelöscht werden und man kommt um ein DELETE Statement nicht herum, steht meistens schon das nächste Problem vor der Tür: Der verfügbare Platz im UNDO-Tablespace oder für ArchiveLogs ist eingeschränkt. Um mit diesen Limitierungen zurecht zu kommen, kann folgendes Löschskript verwendet werden, dass die Datensätze einer Tabelle in Blöcken löscht.

Mit den Werten für lBatchSize und lDelaySeconds kann man an der Blockgröße bzw. eine Zwangspause drehen, mit der sich die Auswirkung auf den UNDO-Tablespace oder die Fast Recovery Area in den Griff kriegen lässt.

SET SERVEROUTPUT ON

DECLARE
  /* Change values for schema, tablename */
  lTableName    VARCHAR2(100) := 'INSERT_SCHEMA_OWNER.INSERT_TABLE_NAME';
  
  /* Change values for search criteria, batch size and delay if required */
  lWhereClause  VARCHAR2(1000) := '';
  lBatchSize    INTEGER := 100000;
  lDelaySeconds INTEGER := 0;
  
  /* Just a few working variables */
  lLoopCounter  INTEGER := 0;
  lRowsLeft     INTEGER;
  lDeleteStmt   VARCHAR2(1000);
  lCountStmt    VARCHAR2(1000);
BEGIN
  DBMS_OUTPUT.PUT_LINE('START');
  lCountStmt  := 'SELECT COUNT(*) FROM ' || lTableName;
  lDeleteStmt := 'DELETE FROM ' || lTableName || ' WHERE ROWNUM <= ' || lBatchSize;
  IF TRIM(lWhereClause) <> '' THEN
	lDeleteStmt := lDeleteStmt || ' AND (' || lWhereClause || ')';
  END IF;
  
  DBMS_OUTPUT.PUT_LINE('Executing delete statment...');
  DBMS_OUTPUT.PUT_LINE(lDeleteStmt);
  
  LOOP
    lLoopCounter := lLoopCounter + 1;
    EXECUTE IMMEDIATE lDeleteStmt;
    DBMS_OUTPUT.PUT_LINE('Deleted Rows: ' || SQL%rowcount);
    COMMIT;
    EXECUTE IMMEDIATE lCountStmt INTO lRowsLeft;
    DBMS_OUTPUT.PUT_LINE('Remaining Rows: ' || lRowsLeft);
    EXIT WHEN lRowsLeft = 0;
	DBMS_LOCK.SLEEP(lDelaySeconds);
  END LOOP;
END;
/

Führt man das Skript nicht als höher privilegierter User (SYS/SYSTEM) aus, muss ggf. noch die Berechtigung zum Aufruf des Packages DBMS_LOCK an den aufrufenden User vergeben werden:

/* Berechtigung zum Ausführen von DBMS_LOCK */
GRANT execute ON DBMS_LOCK TO SCRIPT_USER;

Suche