oracle大表处理方法很多,现场需求:保留最近30天的,更早的全部清理
表名:T_JSJMXX_CHECKIN
备份表:T_JSJMXX_CHECKIN_BF
--备份表结构
create table T_JSJMXX_CHECKIN_BF as select * from T_JSJMXX_CHECKIN where 1=2;
--
alter table T_JSJMXX_CHECKIN nologging;
--
alter table T_JSJMXX_CHECKIN_BF nologging;
--按照条件将数据插入到备份表中 (不同表需要自己定义筛选条件)
insert into /*+ append */ T_JSJMXX_CHECKIN_BF select * from T_JSJMXX_CHECKIN where to_date(NEED_CHECKIN_DATE,''yyyy-mm-dd'') >= sysdate-30;
--清空业务表
truncate table T_JSJMXX_CHECKIN;
--备份的数据插入回业务表
insert into /*+ append */ T_JSJMXX_CHECKIN select * from T_JSJMXX_CHECKIN_BF;
--删除备份表
drop table T_JSJMXX_CHECKIN_BF;
alter table T_JSJMXX_CHECKIN logging;
commit;