一、问题描述

早上正在上班路上,群里客户说,有一张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                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
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;