通过rowid access的方式将丢失数据文件的表空间重建到别的表空间上

通过rowid access的方式将 丢失数据文件的 表空间 重建到 别的表空间上， 并将原损坏表空间 DROP掉。

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$version;

BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> create tablespace lost datafile '/s01/lost1.dbf' size 10M;

Tablespace created.
SQL> create table losttest tablespace lost as select * from dba_objects;

Table created.
SQL> alter tablespace lost add datafile '/s01/lost2.dbf' size 10M;

Tablespace altered.
SQL> insert /+ append / into losttest select * from losttest;

50722 rows created.

SQL> commit;

Commit complete.

SQL> insert /+ append / into losttest select * from losttest;
insert /+ append / into losttest select * from losttest

• ERROR at line 1:
  ORA-01653: unable to extend table SYS.LOSTTEST by 128 in tablespace LOST

SQL> alter system checkpoint;

System altered.
SQL> alter system flush buffer_cache;

System altered.
SQL> select count(*) from losttest;

COUNT(*)
101444
SQL> select object_id,data_object_id from dba_objects where object_name='LOSTTEST';

OBJECT_ID DATA_OBJECT_ID

53710 53710

SQL> select RELATIVE_FNO,BLOCK_ID,BLOCKS from dba_extents where segment_name='LOSTTEST' order by 1,2;

RELATIVE_FNO BLOCK_ID BLOCKS

6 9 8
6 17 8
6 25 8
6 33 8
6 41 8
6 49 8
6 57 8
6 65 8
6 73 8
6 81 8
6 89 8

RELATIVE_FNO BLOCK_ID BLOCKS

6 97 8
6 105 8
6 113 8
6 121 8
6 129 8
6 137 128
6 265 128
6 393 128
6 521 128
6 649 128
6 777 128

RELATIVE_FNO BLOCK_ID BLOCKS

6 905 128
6 1033 128
7 9 128
7 137 128
7 265 128
7 393 128
7 521 128
7 649 128
7 777 128
7 905 128
7 1033 128

33 rows selected.
SQL> col file_name for a40
SQL> select RELATIVE_FNO,FILE_ID,FILE_NAME from dba_data_files where TABLESPACE_NAME='LOST';

RELATIVE_FNO FILE_ID FILE_NAME

6 6 /s01/lost1.dbf
7 7 /s01/lost2.dbf

RELATIVE_FNO 6 /s01/lost1.dbf rowid from block 9 to (1033+128)
RELATIVE_FNO 7 /s01/lost2.dbf rowid from block 9 to (1033+128)

示例表 losttest 在 RFN 6 上的存储范围是 从 9号块到 1033+128号块(不包括该块)
示例表 losttest 在 RFN 7 上的存储范围是 从 9号块到 1033+128号块(不包括该块)

我们利用以上信息构建 rowid

rowid between dbms_rowid.rowid_create(1,53710,6,9,0) and dbms_rowid.rowid_create(1,53710,6,1033+128,0)
SQL> alter database datafile '/s01/lost2.dbf' offline drop;

Database altered.
SQL> select count(*) from losttest;
select count(*) from losttest

• ERROR at line 1:
  ORA-00376: file 7 cannot be read at this time
  ORA-01110: data file 7: '/s01/lost2.dbf'

SQL> set linesize 140 pagesize 1400
SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

7 OFFLINE OFFLINE 1533717 25-APR-12
SQL> select dbms_rowid.rowid_create(1,53710,6,9,0) from dual;

DBMS_ROWID.ROWID_C
AAANHOAAGAAAAAJAAA ==> 这是RFN 6上的 低位ROWID
SQL> select dbms_rowid.rowid_create(1,53710,6,1033+128,0) from dual;

DBMS_ROWID.ROWID_C
AAANHOAAGAAAASJAAA ==> 这是RFN 6上的 高位ROWID

注意 不能让SQL FULL Table Scan Losttest ，全表扫描必然触发 ORA-00376错误。 需要让SQL走 rowid access的路径，所以要用到 /+ ROWID(A) /的 HINT

因为RFN 6上的ROWID 总是小于 RFN 7上的rowid，所以我们仅指定 小于 上面获得的高位ROWID即可
SQL> select /+ ROWID(A) / count(*) from losttest A where rowid<';

COUNT(*)
82158
SQL> create table backup_losttest tablespace users as select /+ ROWID(A) / * from losttest A where rowid<';

Table created.
SQL> select count(*) from backup_losttest;

COUNT(*)
82158

SQL> drop table losttest purge;

Table dropped.
SQL> drop tablespace lost;

Tablespace dropped.