最近在做一個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;

資料檔案offline不會影響索引分割槽的狀態，分割槽相關的段也是存在的，索引並沒有察覺到相關的資料檔案已離線

select * from user_ind_partitions;

select * from user_segments;

4.rebuild索引分割槽

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），可以成功

alter index idx_test_c2 rebuild partition sys_p872 tablespace dasong_idx3;

rebuild成功可以看到索引分割槽狀態為usable，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;

索引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;
commit;

6.刪除索引、刪除表空間

最後只能先刪除索引，再刪除表空間，才能清理完對應的資料字典資料。

drop index idx_test_c2;
drop tablespace dasong_idx2 including contents and datafiles;

暫時沒有想出其它的方法來解決這個問題，不過temporary沒有清理，也不會影響索引部分分割槽的rebuild（先unusable，再rebuild），只是看起來比較不舒服，如果可以接受這個的話，此方案還是可以考濾的，畢竟入庫速度有好幾倍的提升。

資料檔案offline後unusable索引造成的問題