快速清理几十亿的oracle表数据

最新推荐文章于 2024-03-09 18:06:06 发布

原创最新推荐文章于 2024-03-09 18:06:06 发布 · 置顶 · 518 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#oracle #数据库

本文介绍了如何在Oracle中管理数据删除流程，包括创建表结构、设置索引、定时删除及监控执行状态。

1.创建进度跟踪表

1.1创建序列

create sequence seq_del_table minvalue 1 maxvalue 999999999999999999 start with 1 increment by 1 nocache;

1.2创建进度跟踪表

create table bospfy.deleteTableProcess
(
id INTEGER primary key not null,
tableName VARCHAR2(200),
beginTime date,
endTime date,
delCount INTEGER,
costSenconds NUMBER,
inTime timestamp
)
tablespace NNC_DATA01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 256K
next 256K
minextents 1
maxextents unlimited
pctincrease 0
);

2.查看表的索引

查看删除条件字段的索引情况，如果没有该字段的索引，请先创建相关索引

create index HADES_AR.I_FM_ACCOUNT_FEESRCDETAIL_001
on HADES_AR.FM_ACCOUNT_FEESRCDETAIL ( to_date(CREATIONTIME,'YYYY-MM-DD HH24:MI:SS'), CHARGE_AGAINST_FLAG)
online parallel 4
tablespace NNC_INDEX01
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);

3.按时间循环删除数据

declare
start_time PLS_INTEGER;
end_time PLS_INTEGER;
duration_in_seconds NUMBER(20,0);

delCount INTEGER;

v_begin_time date;
v_end_time date;
begin
DBMS_OUTPUT.ENABLE(buffer_size => null);
select min(CREATE_TIME),max(CREATE_TIME) into v_begin_time,v_end_time from hades_worker.HDS_ADV_B_WORKER_TASK;
v_begin_time := v_begin_time - 1;
v_end_time := v_end_time +1;
DBMS_OUTPUT.PUT_LINE('开始清理 hades_worker.HDS_ADV_B_WORKER_TASK 表符合条件的数据');
while v_begin_time < v_end_time LOOP
begin
start_time := DBMS_UTILITY.GET_TIME;

delete from hades_worker.HDS_ADV_B_WORKER_TASK where CREATE_TIME>=v_begin_time and CREATE_TIME<=v_begin_time+1 and status in (3 ,4);
delCount :=SQL%ROWCOUNT;
commit;

end_time := DBMS_UTILITY.GET_TIME;

duration_in_seconds := (end_time - start_time) /100;

insert into bospfy.deleteTableProcess(id,tableName,beginTime,endTime,delCount,costSenconds,inTime)
values(seq_del_table.nextval,'hades_worker.HDS_ADV_B_WORKER_TASK',v_begin_time,v_begin_time+1,delCount,duration_in_seconds,SYSDATE);

v_begin_time := v_begin_time + 1;
end;
end LOOP;
DBMS_OUTPUT.PUT_LINE('完成清理 hades_worker.HDS_ADV_B_WORKER_TASK 表符合条件的数据');
end;

4.查询语句执行进度

select sid, opname, target, target_desc, sofar, totalwork, trunc(sofar/totalwork*100,2) || '%' as perwork,
to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') start_time, elapsed_seconds elapsed,
executions execs, buffer_gets/decode(executions, 0, 1, executions) bufgets, module, sql_text
from vsession_longops sl, vsqlarea sa
where sl.sql_hash_value = sa.hash_value
and upper(substr(module, 1, 4)) <> 'RMAN'
and substr(opname, 1, 4 ) <> 'RMAN'
and sl.start_time > trunc(sysdate)
order by sid;

5.查询当前已删除进度

select * from bospfy.deleteTableProcess t
--WHERE upper(TABLENAME) = upper('HADES_WORKER.HDS_ADV_B_WORKER_TASK')
order by id desc;

6.统计整体删除量

select count(1),sum(delcount) from bospfy.deleteTableProcess
--WHERE TABLENAME='hades_ar.HDS_REP_WRITE_OFF_DETAIL';

经过上述过程，就可以实现快速删除海量数据。如有多张表，只要条件索引存在，并行开多窗口执行，能够加速删除进程。