DELETE FROM table_name WHERE date_column < DATE '2008-01-01'

select 'delete from ' || table_name || ' where date_column < date ''2008-01-01'';' from user_tables;

begin
  for rec in (select table_name from user_tables) loop
    execute immediate 'delete from ' || rec.table_name || ' where date_column < date ''2008-01-01''';
  end loop;
end;
/

ENWEBS1S > select count(*) from site.email_audit;

  COUNT(*)
----------
    461008

ENWEBS1S > @CURSOR_DELETE_PURGE_SITE_EMAIL_AUDIT.sql
100000 lines deleted
100000 lines deleted
100000 lines deleted
100000 lines deleted
460912 rows attempted to delete.
0 errors.
460912 lines deleted.

PL/SQL procedure successfully completed.

ENWEBS1S > select count(*) from site.email_audit;

  COUNT(*)
----------
        96

set termout on
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
DECLARE
   v_sqlerrm number;
   ncount number;
   nerrcount number;
   nrows number;
   CURSOR C1 IS 
select rowid from SITE.EMAIL_AUDIT where status='SENT' and uuid is not null and date_requested < sysdate-3 and rownum < 500001;
BEGIN
   ncount:=0;
   nerrcount:=0;
   nrows:=0;
   FOR III IN C1 LOOP
      ncount := ncount + 1;
      /* DBMS_OUTPUT.PUT_LINE(to_char(III.rowid)) */
      begin
   delete from SITE.EMAIL_AUDIT where rowid=III.rowid;
      EXCEPTION  -- exception handlers begin
   WHEN ZERO_DIVIDE THEN  -- handles 'division by zero' error
      dbms_output.put_line('zero division.');
   WHEN OTHERS THEN  -- handles all other errors
        nerrcount := nerrcount + 1;
        if trunc(nerrcount/100000)=nerrcount/100000 then
        dbms_output.put_line('100000 errors');
        END IF;
        end;
      if trunc(ncount/10)=ncount/10 then 
      commit;                                                    /* you can comment out the commits to do some tests then rollback*/
      END IF;
      if trunc(ncount/100000)=ncount/100000 then 
      DBMS_OUTPUT.PUT_LINE('100000 lines deleted');
      END IF;
   END LOOP;
   commit;                                                      /* you can comment out the commits to do some tests then rollback*/
   dbms_output.put_line(to_char(ncount)||' rows attempted to delete.');
   dbms_output.put_line(to_char(nerrcount)||' errors.');
   nrows:=ncount-nerrcount;
   dbms_output.put_line(to_char(nrows)||' lines deleted.');
END;
/

delete from SITE.EMAIL_AUDIT where status='SENT' and uuid is not null and date_requested < sysdate-3;
commit;

ENWEBP1P > @active
 SID SERIAL# MACHINE      LOGIN           DB     SEC_WAIT USERNAME  EVENT
---- ------- ------------ --------------- ------ -------- --------- ------------------
1954    4761 US\KENDALLA- 07-FEB-13 12:11 NWEBP2        0 ENWEBP2P  gc current request
1832   14525 prodapp04    06-FEB-13 21:39 NWEBP4        0 SITE_USER SQL*Net message to
1010     517 US\KENDALLA- 07-FEB-13 12:09 NWEBP3        0 ENWEBP3P  gc current request

set lines 200
set pages 50
set trunc off
set wrap off
column username print
column username format a11
column program format a60
column machine format a18
column sid format 999999
col sec_wait format 9999999
column serial# format 999999
column login format a15
col db for a10
col sid print
col serial# print
SELECT sid,
       serial#,
       machine,
       To_char(s.logon_time, 'DD-MON-RR HH24:MI') login,
       i.instance_name    db,
       s.seconds_in_wait   sec_wait,
       s.username,
       s.event,
       s.status,
       s.program,
       s.machine,
       s.MODULE,
       s.terminal
FROM   gv$session s,
       gv$instance i
WHERE  i.inst_id = s.inst_id
       AND s.status = 'ACTIVE'
       AND s.username IS NOT NULL
       AND s.wait_class <> 'Idle'
--and seconds_in_wait>0
ORDER  BY seconds_in_wait
/