数据库delete删大表数据导致数据库异常解决方法

–查看回滚参数设置，是否是默认并行
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.

SELECT d.OWNER
,d.OBJECT_NAME
,d.OBJECT_TYPE
,d.status
FROM dba_objects d
WHERE d.status = ‘INVALID’
AND d.owner = ‘**’;

–查询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’;

–查看等待事件
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

rename table to table_20210410;

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;

alter index USER.INDEX1 rename to INDEX1_20210410;
–重建索引
create index USER.INDEX1 on USER.TABLE (***) tablespace TBS parallel 8;
–取消并行
alter index USER.INDEX1 noparallel;

drop table USER.TABLE_20210410 cascade;