drop表中断不释放空间处理

 

一次用plsql developver drop表异常中断，表空间空间不释放，告警日志报如下错误：

 

Errors in file /opt/oracle/db/diag/rdbms/db/db1/trace/db1_smon_16016.trc  (incident=94423):

ORA-00600: internal error code, arguments: [ktecgeb-1], [32], [], [], [], [], [], [], [], [], [], []

Non-fatal internal error happenned while SMON was doing temporary segment drop.

SMON encountered 9 out of maximum 100 non-fatal internal errors.

 

 

找到临时段和文件头编号:

 

SQL> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE from dba_segments where SEGMENT_TYPE='TEMPORARY'; 

 

SEGMENT_NAME          SEGMENT_TYPE          TABLESPACE_NAME       HEADER_FILE

-------------------- -------------------- -------------------- --------------------

16.1144841            TEMPORARY              TBS2                 16.1144841

24.153097             TEMPORARY              TBS2                 24.153097

24.153609             TEMPORARY              TBS2                 24.153609

24.154121             TEMPORARY              TBS2                 24.154121

24.154633             TEMPORARY              TBS2                 24.154633

24.155145             TEMPORARY              TBS2                 24.155145

 

标记这些段为损坏：

 

SQL> exec dbms_space_admin.segment_corrupt('TBS2',16,1144841);

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_space_admin.segment_corrupt('TBS2',24,153097);

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_space_admin.segment_corrupt('TBS2',24,153609);

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_space_admin.segment_corrupt('TBS2',24,154121);

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_space_admin.segment_corrupt('TBS2',24,154633);

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_space_admin.segment_corrupt('TBS2',24,155145);

 

PL/SQL procedure successfully completed

 

然后删除这些段：

 

SQL> exec dbms_space_admin.segment_drop_corrupt('TBS2',16,1144841);

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_space_admin.segment_drop_corrupt('TBS2',24,153097);

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_space_admin.segment_drop_corrupt('TBS2',24,153609);

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_space_admin.segment_drop_corrupt('TBS2',24,154121);

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_space_admin.segment_drop_corrupt('TBS2',24,154633);

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_space_admin.segment_drop_corrupt('TBS2',24,155145);

 

PL/SQL procedure successfully completed

 

 

重建这些段所在表空间位图：

SQL> exec dbms_space_admin.tablespace_rebuild_bitmaps('TBS2');

 

PL/SQL procedure successfully completed

 

检查表空间，空间已经释放，告警日志也再没有报错。此故障处理完毕。