大表delete删数据导致数据库异常解决
目录
- 一、问题描述
- 二、查看数据库情况
- 三、提出方案
- 四、执行过程
一、问题描述
早上正在上班路上,群里客户说,有一张24G的大表,delete删了26小时还没有跑完,目前进程还在跑让帮忙处理下,停止当前进程,并保留对应条件的数据,多余数据删掉。
二、查看数据库情况
1.未开归档
2.UNDO表空间爆满
三、提出方案
1.经过沟通,该表为测试数据表,目前没有在使用;
于是打算rename该表,通过ctas的方式去将需要的数据捞出来,重建索引和相关约束。
2.存在一个问题:delete进程已经执行了26小时,如果kill进程,必然会导致undo回滚,回滚默认开启并行会导致数据库和系统负载很高,于是需要提前处理下,设置参数_fast_start_parallel_rollback_关掉UNDO并行。
--查看回滚参数设置,是否是默认并行 SQL> show parameter fast_start_parallel_rollback NAME TYPEVALUE ------------------------------------ ----------- ------------------------------ fast_start_parallel_rollback string LOW --LOW 代表 开启CPU*2并行 --设置spfile取消并行 SQL> alter system set fast_start_parallel_rollback=false; System altered.
四、执行过程
1.备份保留 表创建语句。
2.记录当前无效对象。
SELECT d.OWNER ,d.OBJECT_NAME ,d.OBJECT_TYPE ,d.status FROM dba_objects d WHERE d.status = "INVALID" AND d.owner = "**";
3.查看delete进程pid,系统级kill掉。
--查询SID, SERIAL# select s.SQL_TEXT, se.SID, se.SERIAL# from v$session se, v$sql s where s.sql_id = se.SQL_ID and username = "**" and status = "ACTIVE" and s.SQL_TEXT like "%delete * from *****%"; --数据库级kill进程 alter system kill session "2953,31083";
报错
ERROR at line 1:
ORA-00031: session marked for kill
--系统级 kill进程
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=2953;
kill -9 105257
4.观察undo回滚情况
--查看等待事件 select inst_id,event,count(*) from gv$session a where a.status="ACTIVE" and not (a.type = "BACKGROUND" and a.state="WAITING" and a.wait_class="Idle") group by inst_id,event order by a.inst_id,count(*) desc; --------------------------------------------------- 1 wait for a undo record 61 1 resmgr:cpu quantum 29 1 enq: RO - fast object reuse 22 1 enq: CR - block range reuse ckpt 20 1 free buffer waits 17 --查看当前数据回滚情况 alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"; select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal - undoblocksdone "ToDo", decode(cputime, 0, "unknown", sysdate + (((undoblockstotal - undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete" from v$fast_start_transactions where state = "RECOVERING"; --------------------------------------------------- 83 RECOVERING 9026713 455 9026258 26-4月 -21
5.观察系统负载,IO负载,内存使用情况
6.等待回滚完成,持续时间较久。
7.回滚成功后,rename 原表。
rename table to table_20210410;
8.CTAS将需要的数据捞到新表(原来的表名)中。
create table USER.TABLE nologging parallel 8 tablespace TBS as select /*+parallel(TABLE,"8")*/ * from USER.TABLE_20210410 where *** like "******%"; --取消并行 alter table USER.TABLE noparallel; --开启logging alter table USER.TABLE logging;
9.rename索引,并重建索引。
alter index USER.INDEX1 rename to INDEX1_20210410; --重建索引 create index USER.INDEX1 on USER.TABLE (***) tablespace TBS parallel 8; --取消并行 alter index USER.INDEX1 noparallel;
10.创建约束,授权。
11.比对无效对象,数据是否一致
12.drop掉rename的表。
drop table USER.TABLE_20210410 cascade;
以上就是大表delete删数据导致数据库异常解决的详细内容,更多关于delete删数据数据库异常的资料请关注其它相关文章!