如何彻底删除oracle的数据库（值得一看的Oracle生产数据库大表删除方案）

火客 2023-04-09 22:09:14 456

如何彻底删除oracle的数据库（值得一看的Oracle生产数据库大表删除方案）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表创建索引、触发器记得需要重命名。alter table BN_SEQUENCE rename to BN_SEQUENCE_ARCH; alter table BN_SEQUENCE_BAK rename to BN_SEQUENCE; 5、数据补录把前面插入数据后到切换表后的数据做一下补录。相关sql:--查看表上触发器定义 SELECT * FROM DBA_TRIGGERS WHERE TABLE_NAME='BN_SEQUENCE'; SELECT DBMS_METADATA.GET_DDL('TRIGGER'

概述

有这么个需求，有一张5000万大表，需要保留最后3个月数据，也就是1000万数据，而这张表使用很频繁，生产环境也是7*24小时不停，如果用分段delete影响的时间太长，所以用了rename切换的方法。这里先在测试数据库做一下演练。

以测试环境BN_SEQUENCE表做测试，数据量大约是6千万。

如何彻底删除oracle的数据库（值得一看的Oracle生产数据库大表删除方案）(1)

思路

如何彻底删除oracle的数据库（值得一看的Oracle生产数据库大表删除方案）(2)

最近有点喜欢上画图，感觉形象点，可能有点丑，大家不要介意~

实现方案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 table create 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 table create 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') and update_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') and update_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') and update_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') and update_date <to_date('2019/06/19 00:00:00' 'yyyy/mm/dd hh24:mi:ss');

如何彻底删除oracle的数据库（值得一看的Oracle生产数据库大表删除方案）(3)

4、切换表

这里实际上我在生产环境做切换也踏坑了，没考虑到有物化视图这种情况，所以导致切换不了。

alter table BN_SEQUENCE rename to BN_SEQUENCE_ARCH; alter table BN_SEQUENCE_BAK rename to BN_SEQUENCE;

如何彻底删除oracle的数据库（值得一看的Oracle生产数据库大表删除方案）(4)

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')

如何彻底删除oracle的数据库（值得一看的Oracle生产数据库大表删除方案）(5)

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、校验数据

如何彻底删除oracle的数据库（值得一看的Oracle生产数据库大表删除方案）(6)