表空間遷移辦法補充

 

上次總結了表空間遷移辦法（http://blog.itpub.net/12932950/viewspace-741543），發現還存在幾個不足的地方：
1、沒有考慮iot的情況。對於iot，是不能rebuild其主鍵索引的，要move表才行；
2、沒有考慮存在long欄位的表。這類表不能直接做move，直接處理就報錯了；

同時，也有人提到了用exp/imp的可傳輸表空間方法來實現遷移。
但我覺得既然要exp/imp了，那就乾脆進一步，用expdp/impdp的remap_tablespace了，exp/imp反而不方便，還要建臨時表空間什麼的，多費力。
具體可參考我的另一個blog《資料泵實現資料遷移到異地庫》（http://blog.itpub.net/12932950/viewspace-752301/）。

言歸正傳，這次對前面提到的這兩個問題做了補充：
1、檢查是否為iot，如果是，則排除對iot的主鍵索引的rebuild，改為對錶的move；
2、增加條件不能存在long型別的欄位，存在則單獨處理；

修改後的sql如下：
with tmp as(
select a.owner, a.TABLE_NAME, c.segment_name, c.segment_type, c.tablespace_name
   from dba_tables a, dba_constraints b, dba_segments c
  where b.constraint_name = c.segment_name
    and b.owner = c.owner
    and b.constraint_type = 'P'
    and a.TABLE_NAME = b.table_name
    and a.OWNER = b.owner
    and a.iot_type = 'IOT')
select segment_type, segment_name, partition_name,
        case segment_type
        when 'TABLE' then 'alter table ' || owner || '.' || segment_name || ' move tablespace dbs_temp;'
        when 'INDEX' then 'alter index ' || owner || '.' || segment_name || ' rebuild tablespace dbs_temp;'
        when 'INDEX PARTITION' then 'alter index ' || owner || '.' || segment_name || ' rebuild tablespace dbs_temp;'
        when 'TABLE PARTITION' then 'alter table ' || owner || '.' || segment_name || ' move partition ' || partition_name || ' tablespace dbs_temp;'
        end sqltext
   from dba_segments b
  where not exists(select 1 from tmp a where a.segment_name = b.segment_name and a.owner = b.owner)
    and tablespace_name = 'MOVE_TBS'
    and segment_type not like 'LOB%'
    and segment_name not like 'BIN%'
    and not exists(select 1 from dba_tab_columns a
                    where (a.data_type like 'LONG%' or a.data_type like '%LOB%')
                      and a.table_name = b.segment_name
                      and a.owner = b.owner
                      and b.segment_type like 'TABLE%')
  union all
select segment_type, segment_name, null, 'alter table ' || table_name || ' move tablespace dbs_temp;'
   from tmp
  where tablespace_name = 'MOVE_TBS'

對於存在long型欄位的表，首先篩選出來：
select *
   from dba_segments b
  where tablespace_name = 'FUND_TABLE'
    and segment_name not like 'BIN%'
    and exists(select 1 from dba_tab_columns a
                where a.data_type like 'LONG%'
                  and a.table_name = b.segment_name
                  and a.owner = b.owner
                  and b.segment_type like 'TABLE%')

然後，用exp/imp匯出再匯入即可，當然，也有提到的用sqlplus copy也是一個不錯的辦法，還能減去中間dmp檔案的過程，直接以資料流的形式實現。
不過，基於一個很簡單的原因，sqlplus copy必須變更表名或變更使用者名稱。

對於lob欄位的處理可見前一篇帖子的內容，在這裡不做贅述。