大表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 ;