惜分飞

在数据库的使用中你可能注意到了,在某些情况下,可能出现某些segment的type为TEMPORARY，名字为file#.block#的格式的对象.这里我通过试验重现该对象并且给出清理方法
创建表空间/用户

SQL> create tablespace test datafile 
  2  '/u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf'
  3  size 128M autoextend on;

Tablespace created.

SQL> create user chf identified by oracle;

User created.

SQL> grant dba to chf;

Grant succeeded.

创建模拟对象
本次通过一个index对象经过一些列操作使其变为我们需要的TEMPORARY对象

SQL> conn chf/oracle
Connected.
SQL> create table t_xifenfei tablespace test
  2  as select * from dba_objects;

Table created.

SQL> create index idx_t_xifenfei on t_xifenfei(object_id) tablespace test;

Index created.

SQL> select header_file,header_block from DBA_SEGMENTS 
  2   where segment_name='IDX_T_XIFENFEI' AND OWNER='CHF';

HEADER_FILE HEADER_BLOCK
----------- ------------
          5          267

破坏segment header
这里通过dd 把该block重置为空块,然后rman检查坏块,证明我们处理正常把index的segment header弄成了空块

SQL> select name from v$datafile where file#=5;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@xffdbrh5 ~]$ dd if=/dev/zero 
>of=/u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf 
>bs=8192 count=1 seek=267 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000100548 seconds, 81.5 MB/s
[oracle@xffdbrh5 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 21 16:30:56 2015

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  901775360 bytes
Fixed Size                  2024944 bytes
Variable Size             138414608 bytes
Database Buffers          759169024 bytes
Redo Buffers                2166784 bytes
Database mounted.
Database opened.

[oracle@xffdbrh5 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Dec 21 16:33:00 2015

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORA10G (DBID=4205072872)

RMAN> backup validate check logical datafile 5;

Starting backup at 21-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-DEC-15

RMAN> exit


Recovery Manager complete.
[oracle@xffdbrh5 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 21 16:33:50 2015

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from V$DATABASE_BLOCK_CORRUPTION ;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         5        267          1                  0 ALL ZERO

index segment header异常
index异常后,通过rebuild报ora-08103错误

SQL> alter index chf.idx_t_xifenfei rebuild;
alter index chf.idx_t_xifenfei rebuild
*
ERROR at line 1:
ORA-08103: object no longer exists

重现TEMPORARY对象
通过删除index,然后发现了我们久违的TEMPORARY类型的对象出现

SQL> drop index chf.idx_t_xifenfei;

Index dropped.

SQL> col SEGMENT_NAME for a20
SQL>  select segment_name,segment_type from user_segments;

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
T_XIFENFEI           TABLE
5.267                TEMPORARY

清理TEMPORARY对象

SQL> CONN / AS SYSDBA
Connected.
SQL> exec dbms_space_admin.segment_corrupt('TEST',5,267);

PL/SQL procedure successfully completed.

SQL> exec dbms_space_admin.segment_drop_corrupt('TEST',5,267);

PL/SQL procedure successfully completed.

SQL> select segment_name,segment_type from DBA_SEGMENTS WHERE OWNER='CHF';

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
T_XIFENFEI           TABLE

通过试验证明:当segment header异常,并且删除该对象,就会出现type为TEMPORARY，名字为file#.block#的格式的对象.我们可以通过dbms_space_admin包处理该类异常对象,让他们彻底从数据库中清除掉