關於move tablespace的問題總結

現在得到一個需求，需要把生產環境的多個schema下的表結構複製到測試環境中的一個schema下。
生產環境和測試i環境的表空間配置都不一樣。
目前可以考慮用如下的幾種方式來實現。
1）使用exp/imp來做表結構的匯出匯入。
2）使用dbms_metadata.get_ddl來生成對應的ddl語句。
3）使用expdp/impdp來做表結構的匯入匯出。
因為涉及的表大約有2000個，採用dbms_metadata是一個很大的工作量而且在多個schema中需要權衡，所以沒有采用。
expdp需要在服務端配置directory，客戶要求只能在備庫上做匯出，備庫是在read only狀態下的，所以一下子切斷了使用expdp的希望。
最後只能使用傳統的exp/imp來做了，根據我的經驗，這方面exp/imp的速度一點也不遜色。
exp prod_user1/prod_user1 file=prod_user1.dmp buffer=9102000 log=prod_user1.log statistics=none indexes=y constraints=y grants=n rows=n
exp prod_user3/prod_user2 file=prod_user2.dmp buffer=9102000 log=prod_user2.log statistics=none indexes=y constraints=y grants=n rows=n
exp prod_user3/prod_user3 file=prod_user3.dmp buffer=9102000 log=prod_user3.log statistics=none indexes=y constraints=y grants=n rows=n

匯出很快就做完了，然後壓縮打包，看起來很順利。
在嘗試匯入的時候，發現tablespace不匹配，因為測試環境和生產環境的表空間不同，而且因為lob欄位的原因，會固執的去找原來的表空間。這個時候就想起來impdp的remap_tablespace的優點了。
但是沒法用，最後就臨時建了和生產類似的表空間，準備先把表導進去再說，然後再做move的操作，對於move tablespace的時候，需要考慮表和索引，對於表，如果沒有lob欄位，就可以直接使用move 操作（因為只有表結構沒有資料)，如果含有lob欄位，則需要指定lob列做move操作，對於索引而言move操作就不可用了，需要使用rebuild
如果沒有lob欄位,表的move操作就不多說了，類似下面的樣子,就把表移到了large_data這個表空間裡。
alter table xxxx move tablespace large_data;
對於索引，可以採用如下的方式，就索引在large_data中進行了重建。
alter index xxxx rebuild tablespace large_data;
如果表中含有lob欄位，則需要指定lob列，lob欄位會自動建立資料段，索引段，如果嘗試rebuild lob索引時會報如下的錯誤。
alter index SYS_IL0002310750C00009$$ rebuild tablespace large_data
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

對於lob的地方，多說一些。如果透過資料字典,user_ind_columns來檢視，是沒有結果的，需要到user_lobs中去查詢。

SQL> select index_name,table_name from user_indexes where index_name='SYS_IL0002310750C00009$$';

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
SYS_IL0002310750C00009$$       APP_XML_CONFIG

SQL> select index_name,column_name from user_ind_columns where index_name='SYS_IL0002310750C00009$$';

no rows selected

select table_name,column_name,tablespace_name,index_name from user_lobs where index_name='SYS_IL0002310750C00009$$'
SQL> /

TABLE_NAME                     COLUMN_NAME                    TABLESPACE_NAME                INDEX_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
APP_XML_CONFIG                 XML                            DATAL01                        SYS_IL0002310750C00009$$

可以使用下面形式的語句來做lob欄位的遷移。

SQL> alter table app_xml_config move tablespace large_data lob(xml) store as lobsegment(tablespace large_data);

Table altered.

對於上千張表來說，使用如下的sql能夠自動生成move tablespace的語句。

--對錶中的非lob列進行move tablespace操作
select 'alter table '|| table_name||' move tablespace large_data;' from user_tables t where tablespace_name!='LARGE_DATA' and exists ( select null from user_tab_cols where table_name=t.table_name and data_type not in ('LONG','BLOB','CLOB'));
 --對錶中的lob 列進行move tablespace操作
select 'alter table '||table_name||' move tablespace large_data lob('||column_name||') store as '||SEGMENT_NAME||'(tablespace large_data);' from user_lobs where index_name in (select index_name from user_indexes where tablespace_name!='LARGE_DATA');
--對錶中的索引進行rebuild，因為lob資料段已經做了move 操作，對於索引lob段就不用再做move 操作了。

select 'alter index '||index_name||' rebuild tablespace large_data;' from user_indexes where tablespace_name!='LARGE_DATA';