批量删除oracle库的表 值得一看的Oracle生产数据库大表删除方案
概述
有这么个需求,有一张5000万大表,需要保留最后3个月数据,也就是1000万数据,而这张表使用很频繁,生产环境也是7*24小时不停,如果用分段delete影响的时间太长,所以用了rename切换的方法。这里先在测试数据库做一下演练。
以测试环境BN_SEQUENCE表做测试,数据量大约是6千万。
思路
最近有点喜欢上画图,感觉形象点,可能有点丑,大家不要介意~
实现方案1、获取A表定义、索引、触发器、外键约束
这里的表定义、索引、主外键实际上用PLSQL就可以直接看到了,所以就不写了,只写了触发器的。
相关sql:
--查看表上触发器定义SELECT * FROM DBA_TRIGGERS WHERE TABLE_NAME='BN_SEQUENCE';SELECT DBMS_METADATA.GET_DDL('TRIGGER','CHK_BIU_BN_SEQUENCE','GLOGOWNER') FROM DUAL;SELECT DBMS_METADATA.GET_DDL('TRIGGER','BN_SEQUENCE_PN','GLOGOWNER') FROM DUAL;-- Create tablecreate table BN_SEQUENCE( BN_RULE_GID VARCHAR2(101 CHAR) not null, BN_CONTEXT VARCHAR2(300 CHAR) not null, BN_SEQUENCE_ID VARCHAR2(50 CHAR) not null, CURVALUE VARCHAR2(50 CHAR), DOMAIN_NAME VARCHAR2(50 CHAR) not null, INSERT_USER VARCHAR2(128 CHAR) not null, INSERT_DATE DATE not null, UPDATE_USER VARCHAR2(128 CHAR), UPDATE_DATE DATE)tablespace DATA pctfree 10 initrans 1 maxtrans 255 storage ( initial 1 next 1 minextents 1 maxextents unlimited pctincrease 0 );.....
2、创建B表--BN_SEQUENCE_BAK
--这里只创建表定义,不加约束、索引、触发器、外键-- Create tablecreate table BN_SEQUENCE_BAK( BN_RULE_GID VARCHAR2(101 CHAR) not null, BN_CONTEXT VARCHAR2(300 CHAR) not null, BN_SEQUENCE_ID VARCHAR2(50 CHAR) not null, CURVALUE VARCHAR2(50 CHAR), DOMAIN_NAME VARCHAR2(50 CHAR) not null, INSERT_USER VARCHAR2(128 CHAR) not null, INSERT_DATE DATE not null, UPDATE_USER VARCHAR2(128 CHAR), UPDATE_DATE DATE)tablespace DATA pctfree 10 initrans 1 maxtrans 255 storage ( initial 1 next 1 minextents 1 maxextents unlimited pctincrease 0 );
3、分段insert
为了避免对线上环境的影响,建议分段insert,插入最近3个月的数据。
insert into BN_SEQUENCE_BAK select * from BN_SEQUENCE where update_date >=to_date('2019/08/19 00:00:00', 'yyyy/mm/dd hh24:mi:ss') andupdate_date <to_date('2019/09/19 14:00:00', 'yyyy/mm/dd hh24:mi:ss');insert into BN_SEQUENCE_BAK select * from BN_SEQUENCE where update_date >=to_date('2019/07/19 00:00:00', 'yyyy/mm/dd hh24:mi:ss') andupdate_date <to_date('2019/08/19 00:00:00', 'yyyy/mm/dd hh24:mi:ss');insert into BN_SEQUENCE_BAK select * from BN_SEQUENCE where update_date >=to_date('2019/06/19 00:00:00', 'yyyy/mm/dd hh24:mi:ss') andupdate_date <to_date('2019/07/19 00:00:00', 'yyyy/mm/dd hh24:mi:ss');insert into BN_SEQUENCE_BAK select * from BN_SEQUENCE where update_date >=to_date('2018/06/19 00:00:00', 'yyyy/mm/dd hh24:mi:ss') andupdate_date <to_date('2019/06/19 00:00:00', 'yyyy/mm/dd hh24:mi:ss');
4、切换表
这里实际上我在生产环境做切换也踏坑了,没考虑到有物化视图这种情况,所以导致切换不了。
alter table BN_SEQUENCE rename to BN_SEQUENCE_ARCH;alter table BN_SEQUENCE_BAK rename to BN_SEQUENCE;
5、数据补录
把前面插入数据后到切换表后的数据做一下补录。
insert into BN_SEQUENCE select * from BN_SEQUENCE_ARCH where update_date >=to_date('2019/09/19 14:00:00', 'yyyy/mm/dd hh24:mi:ss')
6、B表创建索引、触发器
记得需要重命名。
-- Add comments to the table comment on table BN_SEQUENCE is 'This table stores the current sequence value of the business number.';-- Add comments to the columns comment on column BN_SEQUENCE.BN_RULE_GID is 'BN_RULE_GID contains the unique identifier for the Business Number (BN) rule.';-- Create/Recreate primary, unique and foreign key constraints alter table BN_SEQUENCE add constraint PK_BN_SEQUENCE primary key (BN_RULE_GID, BN_CONTEXT, BN_SEQUENCE_ID) using index tablespace INDX pctfree 10 initrans 2 maxtrans 255 storage ( initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0 );alter table BN_SEQUENCE add constraint FK_BN_SEQRULE_GID foreign key (BN_RULE_GID) references BN_RULE (BN_RULE_GID);-- Grant/Revoke object privileges grant select, insert, update, delete on BN_SEQUENCE to APP_USER;grant select on BN_SEQUENCE to APP_USER_SELECT;grant select, insert, update, delete on BN_SEQUENCE to EXT_USER;....
7、校验数据
结果:数据全部迁移了,保留了去年6月19号到现在的数据,整个过程10分钟
8、drop表
建议保留一段时间后再执行。
觉得有用的朋友多帮忙转发哦!后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~
- 12-24红米k50至尊版怎么从良好变成极佳?看了大米评测感觉红米k50更香了
- 04-30word中如何设置行距?word行距怎么设置
- 01-22李健王菲什么时候认识的?王菲参加李健演唱会旧照
- 02-08三年级计算题丢分怎么办?三年级数学期中考完了
- 12-01吉利造车需要多少钱:吉利又推了个新牌子又一造车新势力到来
- 02-01五年级上册语文1-8单元基础知识:第一二单元知识点梳理
- 03-30汉字结构类型怎么区分?常用汉字结构解读之再
- 12-08高中物理简谐运动公式大全:高中物理,简谐运动
- 05-11脂肪肝饮食方面要注意什么?改善脂肪肝应该如何饮食
- 05-08砷元素有味道吗?正邪兼修的砷元素到底是我们的敌人
- 04-04平面广告行业前景怎么样?传统平面媒体又见行业春天
- 05-30半夜国道车胎爆救援之后怎么处理?强烈谴责,大雾天使坏青州颐德花园30多辆车惨遭扎胎
- 04-06交通秩序整治工程进展:营山县启动交通秩序大整治
- 04-06如何走出低谷期的句子?40条让你从艰难时走出困惑的句子
- 02-06真正殡仪馆抬尸人工资高吗?月薪高达三万多的抗尸人
- 11-24要怎么写好2023高考作文?2023高考作文如果你是老师
热门
推荐
- 1补气活血的中药方剂224
- 2美术鉴赏课教学给教师的建议288
- 3软件开发合同常用范本492
- 4孕期可减少胎儿缺陷几率注意事项116
- 52020年浙江交通职业技术学院招生专业一览表380
- 6描写热爱祖国的诗歌180
- 7关于前进与后退的说明文作文237
- 8苹果耳机声音变小了怎么回事220