最近在做一個oracle入庫速度測試時，想到将最近一個小時的索引放到記憶體中（表是按小時分區）是不是會提升入庫的速度，索引的維護對io是一個不小的開銷；
不過這個方案如果要使用的話資料庫必須是 oracle 12c，因為在目前小時結束後，需要将相關索引移出記憶體，讓下一個小時的索引留在記憶體，這樣記憶體的使用情況基本是一個定量；
而在移動的過程中不能對業務有影響，這樣需要用到12c的新功能，線上移動資料檔案。
測試的結果是入庫速度有很明顯的提升，入庫速度是之前的幾倍，但這個不是本文的重點；本文的重點是在測試的過程中發現的一個小問題，不确定是不是bug（oracle 11.2.0.4也有這個問題），以下是過程。
建立表空間、使用者等
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;
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;
alter database datafile '/oradata/oracle/dasong_idx2.dbf' offline for drop;
資料檔案offline不會影響索引分區的狀态，分區相關的段也是存在的，索引并沒有察覺到相關的資料檔案已離線
select * from user_ind_partitions;
select * from user_segments;
alter index idx_test_c2 rebuild partition sys_p872 tablespace dasong_idx3;
原索引所在資料檔案如果不可用，則rebuild失敗，因為rebuild會從原索引中讀資料，暫不知道怎麼樣繞過
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
先将原來索引分區unusable（SYS_P872）
alter index idx_test_c2 modify partition sys_p872 unusable;
索引分區SYS_P872狀态變成UNUSABLE
分區SYS_P872對應的段消失，多出了一個10.130（這個是原來的SYS_P872對應的段）
現在rebuild索引分區 到 其它表空間（dasong_idx3），可以成功
rebuild成功可以看到索引分區狀态為usable，tablespace是dasong_idx3
對應的新段在dasong_idx3表空間中，原來的10.130并不沒有消失（即使将資料檔案online，重新開機資料庫，10.130也不會消失）
所有使用索引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;
索引idx_test_c2有一個分區在dasong_idx3分區中，是以删除表空間會報錯（rebuild之前應該能删除掉表空間，但是including也會把索引删除，這不是我想要的結果）
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;
最後隻能先删除索引，再删除表空間，才能清理完對應的資料字典資料。
drop index idx_test_c2;
暫時沒有想出其它的方法來解決這個問題，不過temporary沒有清理，也不會影響索引部分分區的rebuild（先unusable，再rebuild），隻是看起來比較不舒服，如果可以接受這個的話，此方案還是可以考濾的，畢竟入庫速度有好幾倍的提升。