oracle 分批删除大量表中数据方法

1,创建新表gps-sq ，保存查询出来表名的rownum 值。

#：  create table gps_sq as select *
              from (select rownum sq , tname
                      from tab
                     where tname like 'GPS_HT_%'
                     order by tname)

2,新建存储过程。

create or replace procedure p_del_gps_ht(a in number,b in number) is
  t_sql varchar2(2000);
begin
  for c in (select tname
              from gps_sq
             where sq >= a
               and sq <= b) loop
 t_sql:='delete from  '||c.tname||' where to_char(location_time,''yyyymmdd'')<=''20140731''';
    execute immediate t_sql;
    commit;
  end loop;
end p_del_gps_ht;

3，执行过程，分批删除数据。

#： execute p_del_gps_ht(1,500)