下面举一个例子来描述该流程。

1、模拟出错情况

SQL> create table t1
2 ( id number(2),
3 name varchar2(15))
4 partition by range (id)
5 (partition p1 values less than (10) tablespace users,
6 partition p2 values less than (20) tablespace test,
7 partition p3 values less than (30)) tablespace shailesh;

SQL> select * from t1;

ID NAME
---------- ---------------
1 XXX
12 XXX
25 XXX

SQL> alter database datafile 'D:ORACLEORADATAORA92test001' offline drop;

Database altered.

Accessing the partitioned table t1 will give the following errror:

SQL> select * from t1;
ERROR:
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: 'D:ORACLEORADATAORA92TEST001'

SQL> drop tablespace test INCLUDING CONTENTS;
drop tablespace test INCLUDING CONTENTS
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

Now you want to get back the partition segment structure (without any data)
and avoid this error.

2、解决方案

1) 首先创建一个结构和分区表一致的临时表，不要有数据。

SQL> create table test as select * from t1 where 1=2;

Table created.

2) 交换这个临时表和出问题的分区。
WITHOUT VALIDATION语法是指临时表中的数据移动到分区中不必检验分区键的分区范围，即
临时表中的数据即使不在p2分区的范围内，也会移动到分区p2中；如果想进行检验的话，就要使
用WITH VALIDATION语法，或者就不写这段，缺省是要检验的。

SQL> ALTER TABLE t1 EXCHANGE PARTITION p2 WITH TABLE test WITHOUT VALIDATION;

Table altered.

3) 现在分区表可以访问了

SQL> select * from t1;

ID NAME
---------- ---------------
1 XXX
25 XXX

4) 删除包含出错数据文件的表空间，再重建一个新的表空间。

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> CREATE TABLESPACE test DATAFILE 'D:ORACLEORADATAORA92test001' SIZE 5M
2 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT manual;

Tablespace created.

5) 将那个表分区Move到新的表空间中去。

SQL> ALTER TABLE t1 MOVE PARTITION p2 TABLESPACE test;

Table altered.

SQL> select * from t1;

ID NAME
---------- ---------------
1 XXX
25 XXX