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

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