数据文件offline后unusable索引造成的问题
最近在做一个oracle入库速度测试时，想到将最近一个小时的索引放到内存中（表是按小时分区）是不是会提升入库的速度，索引的维护对io是一个不小的开销；
不过这个方案如果要使用的话数据库必须是 oracle 12c，因为在当前小时结束后，需要将相关索引移出内存，让下一个小时的索引留在内存，这样内存的使用情况基本是一个定量；
而在移动的过程中不能对业务有影响，这样需要用到12c的新功能，在线移动数据文件。
测试的结果是入库速度有很明显的提升，入库速度是之前的几倍，但这个不是本文的重点；本文的重点是在测试的过程中发现的一个小问题，不确定是不是bug（oracle 11.2.0.4也有这个问题），以下是过程。
1.准备工作
创建表空间、用户等-
create tablespace dasong datafile '/oradata/oracle/dasong.dbf' size 100m;
create tablespace dasong_idx2 datafile '/oradata/oracle/dasong_idx2.dbf' size 100m;
create tablespace dasong_idx3 datafile '/oradata/oracle/dasong_idx3.dbf' size 100m;
create user dasong identified by dasong
default tablespace dasong
temporary tablespace temp;
grant dba to dasong;
grant create session to dasong;
grant resource to dasong;
grant debug connect session to dasong;
grant debug any procedure to dasong;
grant select_catalog_role to dasong;
2.创建表、索引，并插入数据
-
create table t_idx_offline_test
(
c1 number,
c2 number,
c3 number
)
partition by range(c1) interval(1000)
(
partition part_0 values less than(0)
) tablespace dasong;
create index idx_test_c2 on t_idx_offline_test(c2) tablespace dasong_idx2 local;
create index idx_test_c3 on t_idx_offline_test(c3) tablespace dasong_idx3 local;
insert into t_idx_offline_test
select rownum, rownum+1, rownum+2 from dual connect by rownum<10000;
commit;
3.offline数据文件
-
alter database datafile '/oradata/oracle/dasong_idx2.dbf' offline for drop;
-
select * from user_ind_partitions;
-
select * from user_segments;
4.rebuild索引分区
-
alter index idx_test_c2 rebuild partition sys_p872 tablespace dasong_idx3;
-
SQL Error: ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: '/oradata/oracle/dasong_idx2.dbf'
00376. 00000 - "file %s cannot be read at this time"
*Cause: attempting to read from a file that is not readable. Most likely
the file is offline.
*Action: Check the state of the file. Bring it online
-
alter index idx_test_c2 modify partition sys_p872 unusable;
分区SYS_P872对应的段消失，多出了一个10.130（这个是原来的SYS_P872对应的段）
现在rebuild索引分区 到 其它表空间（dasong_idx3），可以成功
-
alter index idx_test_c2 rebuild partition sys_p872 tablespace dasong_idx3;
对应的新段在dasong_idx3表空间中，原来的10.130并不没有消失（即使将数据文件online，重启数据库，10.130也不会消失）
5.temporary段处理
所有使用索引unusable的操作都会使索引原来的分区段变成temporary（unusable索引、删除索引、exchange索引分区对应的数据分区 等）；除非删除表空间，其它常规手段都不会删除temporary段（其实此时索引对应的段应该是已经没有了，或是访问不到的，因为数据文件不可用，此时看到的只是元数据）
- alter index idx_test_c2 unusable;
-
alter index idx_test_c2 modify default attributes tablespace dasong_idx3;
-
drop tablespace dasong_idx2 including contents and datafiles;
-
SQL Error: ORA-14405: partitioned index contains partitions in a different tablespace
14405. 00000 - "partitioned index contains partitions in a different tablespace"
*Cause: An attempt was made to drop a tablespace which contains indexes
whose partitions are not completely contained in this tablespace,
and which are defined on the tables which are completely contained
in this tablespace.
*Action: find indexes with partitions which span the tablespace being
dropped and some other tablespace(s). Drop these indexes, or move
the index partitions to a different tablespace, or find the tables
on which the indexes are defined, and drop (or move) them.
即使 手动删除seg$内容（不确定会不会造成其它问题），user_segments中不再有相关的temporary段，此时删除表空间dasong_idx2还是会报上面的错，说明数据字典没有清理完全，还是有一部分跟dasong_idx2相关（数据库还是认为dasong_idx2有索引数据）
-
delete from seg$ where file#=10 and type#=3;
commit;
6.删除索引、删除表空间
最后只能先删除索引，再删除表空间，才能清理完对应的数据字典数据。-
drop index idx_test_c2;
drop tablespace dasong_idx2 including contents and datafiles;
暂时没有想出其它的方法来解决这个问题，不过temporary没有清理，也不会影响索引部分分区的rebuild（先unusable，再rebuild），只是看起来比较不舒服，如果可以接受这个的话，此方案还是可以考滤的，毕竟入库速度有好几倍的提升。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2122112/，如需转载，请注明出处，否则将追究法律责任。