How do I reduce the logging of my procedure? I have a process that I run every night in our database to take some snapshots of various tables (not full table snapshots, just select record types).

Lately, it has been filling up the redo log and failing.
The statement is below.
Is there anything I can do to reduce the amount of logging this does?

BEGIN

  DELETE FROM DWH_STOCK_SNAPSHOT WHERE CAPTURE_DATE &lt; SYSDATE - 5;
  DELETE FROM DWH_SORD_SNAPSHOT WHERE CAPTURE_DATE &lt; SYSDATE - 5;
  DELETE FROM DWH_SORDD_SNAPSHOT WHERE CAPTURE_DATE &lt; SYSDATE - 5;
  DELETE FROM DWH_SOZD_SNAPSHOT WHERE CAPTURE_DATE &lt; SYSDATE - 5;
  DELETE FROM DWH_PORD_SNAPSHOT WHERE CAPTURE_DATE &lt; SYSDATE - 5;
  DELETE FROM DWH_PORDD_SNAPSHOT WHERE CAPTURE_DATE &lt; SYSDATE - 5;
  DELETE FROM DWH_POZD_SNAPSHOT WHERE CAPTURE_DATE &lt; SYSDATE - 5;
  DELETE FROM DWH_SPRICE_STYLE_SNAPSHOT WHERE CAPTURE_DATE &lt; SYSDATE - 5;
  DELETE FROM DWH_SPRICE_CLRSKU_SNAPSHOT WHERE CAPTURE_DATE &lt; SYSDATE - 5;
  DELETE FROM DWH_SKU_COSTS_SNAPSHOT WHERE CAPTURE_DATE &lt; SYSDATE - 5;

  DWH_SNAPSHOT.TAKE_STOCK_SNAPSHOT();
  DWH_SNAPSHOT.TAKE_SALES_ORDER_SNAPSHOT();
  DWH_SNAPSHOT.TAKE_PURCHASE_ORDER_SNAPSHOT();
  DWH_SNAPSHOT.TAKE_PRICES_SNAPSHOT();
  PDWH_SNAPSHOT.TAKE_COSTS_SNAPSHOT();
END;
As you are completely dropping all the data and rebuilding it, if there is any overlap in the new data and old (especially if there is a lot of overlap - i.e. many of the rows in each table will be the same after the process has completed) then it would be far more efficient to only delete rows that are no longer needed, insert rows that are new to this revised snapshot, and update rows that already exist and need to change.

This could be a lot more code, but it will potentially be much more efficient.

To do this in a DB agnostic way you will need up to three statements to delete, insert and update, wrapped in a transaction to avoid partial results. In many cases updates aren't needed and you only need the delete and insert phases - you will know if this is the case as you know your data better than I.

If portability is not an issue then Oracle supports a MERGE statement that will likely be far more efficient still and result in smaller, easier to maintain, code. See http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm for reference documentation, if that isn't clear you will likely find many examples/tutorials that flesh out the explanation further.