利用PLSQL實現表空間的遷移（二）
在9i的時候，表空間的遷移使用EXP/IMP來實現。在10g中，除了使用EXP/IMP之外，還可以使用資料泵EXPDP/IMPDP，以及RMAN的命令TRANSPORT TABLESPACE命令，除此之外，還可以透過PL/SQL包DBMS_STREAMS_TABLESPACE_ADM來實現。
這一篇介紹PULL_TABLESPACES過程。
利用PLSQL實現表空間的遷移（一）：http://yangtingkun.itpub.net/post/468/484859
前文已經介紹過Oracle的DBMS_STREAMS_TABLESPACE_ADM包提供了8個過程：ATTACH_TABLESPACES、ATTACH_SIMPLE_TABLESPACE、CLONE_TABLESPACES、CLONE_SIMPLE_TABLESPACE、DETACH_TABLESPACES、DETACH_SIMPLE_TABLESPACE、PULL_TABLESPACES、PULL_SIMPLE_TABLESPACE。
其中PULL過程實際上包含了CLONE以及ATTACH兩個過程，其中還包括了DBMS_FILE_TRANSFER包的呼叫，使得在目標資料庫執行一個命令就完成表空間遷移的所有的操作。
這次要遷移的是多個表空間，而且源資料庫和目標資料庫與上一個例子中的完全一致，因此這裡選擇了PULL_TABLESPACES過程。
由於環境和上一個例子完全一致，這裡不需要進行資料庫版本、資料塊大小、以及字符集的檢查，唯一需要檢查的是，需要遷移表空間的一致性。
SQL> SELECT OWNER, TABLESPACE_NAME, SUM(BYTES)/1024/1024
2 FROM DBA_SEGMENTS
3 WHERE OWNER IN ('ZHIFU','DEMO_WH','JSJG','JS_DEV_OPER','DEMO_INFO','PMSADM','JSGOV','JS_DEV','JS_TEST')
4 GROUP BY OWNER, TABLESPACE_NAME;
OWNER TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ ------------------------------ --------------------
JSGOV JSGOV 2778.1875
JSJG JSJG 2230.6875
DEMO_INFO DEMO_WH_TBS 205.5
JS_DEV JSTBS 39215.3125
ZHIFU ZHIFU 1.625
JS_DEV_OPER JSTBS 7.625
DEMO_WH DEMO_WH_TBS 303.875
PMSADM DEMO_WH_TBS 488.4375
JS_TEST JIANGSU 10430.125
9 rows selected.
SQL> SELECT DISTINCT OWNER
2 FROM DBA_SEGMENTS
3 WHERE OWNER NOT IN
4 ('ZHIFU','DEMO_WH','JSJG','JS_DEV_OPER','DEMO_INFO','PMSADM','JSGOV','JS_DEV','JS_TEST')
5 AND TABLESPACE_NAME IN
6 (SELECT TABLESPACE_NAME
7 FROM DBA_SEGMENTS
8 WHERE OWNER IN
9 ('ZHIFU','DEMO_WH','JSJG','JS_DEV_OPER','DEMO_INFO','PMSADM','JSGOV','JS_DEV','JS_TEST'));
no rows selected
SQL> SELECT FILE_NAME
2 FROM DBA_DATA_FILES
3 WHERE TABLESPACE_NAME IN
4 (SELECT TABLESPACE_NAME
5 FROM DBA_SEGMENTS
6 WHERE OWNER IN
7 ('ZHIFU','DEMO_WH','JSJG','JS_DEV_OPER','DEMO_INFO','PMSADM','JSGOV','JS_DEV','JS_TEST'))
8 ORDER BY TABLESPACE_NAME, FILE_ID;
FILE_NAME
---------------------------------------------------------------------
/data/oracle/oradata/kaifa/demowarehouse01.dbf
/data/oracle/oradata/kaifa/jiangsu01.dbf
/data/oracle/oradata/kaifa/jsgov01.dbf
/data/oracle/oradata/kaifa/jsjg01.dbf
/data/oracle/oradata/kaifa/testjs01.dbf
/data/oracle/oradata/kaifa/testjs02.dbf
/data/oracle/oradata/kaifa/testjs03.dbf
/data/oracle/oradata/kaifa/testjs04.dbf
/data/oracle/oradata/kaifa/testjs05.dbf
/data/oracle/oradata/kaifa/testjs06.dbf
/data/oracle/oradata/kaifa/testjs07.dbf
/data/oracle/oradata/kaifa/testjs08.dbf
/data/oracle/oradata/kaifa/testjs09.dbf
/data/oracle/oradata/kaifa/testjs10.dbf
/data/oracle/oradata/kaifa/testjs11.dbf
/data/oracle/oradata/kaifa/testjs12.dbf
/data/oracle/oradata/kaifa/zhifu01.dbf
17 rows selected.
SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK('JSGOV,JSJG,DEMO_WH_TBS,JSTBS,ZHIFU,JIANGSU')
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
檢查目標資料庫：
SQL> SELECT NAME
2 FROM V$TABLESPACE
3 WHERE NAME IN ('JSGOV','JSJG','DEMO_WH_TBS','JSTBS','ZHIFU','JIANGSU');
NAME
------------------------------
JIANGSU
JSTBS
SQL> SELECT DISTINCT OWNER FROM DBA_SEGMENTS
2 WHERE TABLESPACE_NAME IN ('JIANGSU', 'JSTBS');
OWNER
------------------------------
JIANGSU15
JIANGSU
SQL> ALTER TABLESPACE JSTBS RENAME TO JSTBS229;
Tablespace altered.
由於檢查發現目標資料庫和源資料庫在表空間名稱上出現了衝突，因此修改目標資料庫的JSTBS表空間名稱為JSTBS229，另外在源資料庫中修改JIANGSU表空間為JS_TEST：
SQL> ALTER TABLESPACE JIANGSU RENAME TO JS_TEST;
Tablespace altered.
由於表空間出現重名的現象，除了在源資料庫和目標資料庫修改表空間之外，還需要特別注意一個地方，就是資料檔案是否重名的問題：
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
---------------------------------------------------------------------------------
/data/oradata/test08/system01.dbf
/data/oradata/test08/undotbs01.dbf
/data/oradata/test08/sysaux01.dbf
/data/oradata/test08/users01.dbf
/data/oradata/test08/shiyq01.dbf
/data/oradata/test08/js_temp01.dbf
/data/oradata/test08/js_temp02.dbf
/data/oradata/test08/js_trade01.dbf
/data/oradata/test08/js_trade02.dbf
/data/oradata/test08/js_trade03.dbf
/data/oradata/test08/js_ndmain01.dbf
/data/oradata/test08/ndmain01.dbf
/data/oradata/test08/ndmain02.dbf
/data/oradata/test08/ndmain03.dbf
/data/oradata/test08/ndmain04.dbf
/data/oradata/test08/ndmain05.dbf
/data/oradata/test08/gpotbs01.dbf
/data/oradata/test08/gpotbs02.dbf
/data/oradata/test08/gpotbs03.dbf
/data/oradata/test08/gpotbs04.dbf
/data/oradata/test08/gpotbs05.dbf
/data/oradata/test08/testzj01.dbf
/data/oradata/test08/testzj02.dbf
/data/oradata/test08/testzj03.dbf
/data/oradata/test08/testzj04.dbf
/data/oradata/test08/testzj05.dbf
/data/oradata/test08/testzj06.dbf
/data/oradata/test08/testzj07.dbf
/data/oradata/test08/testzj08.dbf
/data/oradata/test08/testzj09.dbf
/data/oradata/test08/testzj10.dbf
/data/oradata/test08/testzj11.dbf
/data/oradata/test08/oldjiangsu01.dbf
/data/oradata/test08/oldjiangsu02.dbf
/data/oradata/test08/jiesuan01.dbf
/data/oradata/test08/jiesuan02.dbf
/data/oradata/test08/jiesuan03.dbf
/data/oradata/test08/newjiangsu01.dbf
/data/oradata/test08/newjiangsu02.dbf
/data/oradata/test08/newjiangsu03.dbf
/data/oradata/test08/newjiangsu04.dbf
/data/oradata/test08/newjiangsu05.dbf
/data/oradata/test08/newjiangsu06.dbf
/data/oradata/test08/js_lyg.dbf
/data/oradata/test08/js_hc.dbf
/data/oradata/test08/test01.dbf
/data/oradata/test08/zj_lpd01.dbf
/data/oradata/test08/info_01.dbf
/data/oradata/test08/info_02.dbf
/data/oradata/test08/jshc_test01.dbf
/data/oradata/test08/TJSQ01.dbf
/data/oradata/test08/TJSQ02.dbf
/data/oradata/test08/TJSQ03.dbf
/data/oradata/test08/TJSQ04.dbf
/data/oradata/test08/sckc01.dbf
55 rows selected.
幸好資料檔名稱沒有出現重複，否則還需要對資料檔名稱進行重新命名。
由於進行過遷移表空間的操作，所有的目錄都已經建立完成，資料庫鏈也已經建立完成，下面需要建立目標資料庫上的目標使用者，首先獲取源資料庫使用者授權的角色和許可權：
SQL> SELECT 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || ';'
2 FROM DBA_ROLE_PRIVS
3 WHERE GRANTEE IN ('ZHIFU','DEMO_WH','JSJG','JS_DEV_OPER','DEMO_INFO','PMSADM','JSGOV','JS_DEV','JS_TEST')
4 ORDER BY GRANTEE, GRANTED_ROLE;
'GRANT'||GRANTED_ROLE||'TO'||GRANTEE||';'
-----------------------------------------------------------------------
GRANT CONNECT TO DEMO_INFO;
GRANT RESOURCE TO DEMO_INFO;
GRANT CONNECT TO DEMO_WH;
GRANT RESOURCE TO DEMO_WH;
GRANT CONNECT TO JSGOV;
GRANT RESOURCE TO JSGOV;
GRANT CONNECT TO JSJG;
GRANT RESOURCE TO JSJG;
GRANT CONNECT TO JS_DEV;
GRANT RESOURCE TO JS_DEV;
GRANT CONNECT TO JS_DEV_OPER;
GRANT RESOURCE TO JS_DEV_OPER;
GRANT CONNECT TO JS_TEST;
GRANT RESOURCE TO JS_TEST;
GRANT CONNECT TO PMSADM;
GRANT RESOURCE TO PMSADM;
GRANT CONNECT TO ZHIFU;
GRANT RESOURCE TO ZHIFU;
18 rows selected.
SQL> SELECT 'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE || ';'
2 FROM DBA_SYS_PRIVS
3 WHERE GRANTEE IN ('ZHIFU','DEMO_WH','JSJG','JS_DEV_OPER','DEMO_INFO','PMSADM','JSGOV','JS_DEV','JS_TEST')
4 AND PRIVILEGE NOT IN
5 (SELECT PRIVILEGE FROM DBA_SYS_PRIVS
6 WHERE GRANTEE IN ('CONNECT', 'RESOURCE'))
7 ORDER BY GRANTEE;
'GRANT'||PRIVILEGE||'TO'||GRANTEE||';'
---------------------------------------------------------------------------------
GRANT CREATE SYNONYM TO DEMO_INFO;
GRANT CREATE VIEW TO DEMO_INFO;
GRANT CREATE DATABASE LINK TO JSGOV;
GRANT CREATE MATERIALIZED VIEW TO JSGOV;
GRANT CREATE SYNONYM TO JSGOV;
GRANT CREATE VIEW TO JSGOV;
GRANT CREATE DATABASE LINK TO JSJG;
GRANT CREATE MATERIALIZED VIEW TO JSJG;
GRANT CREATE SYNONYM TO JSJG;
GRANT CREATE VIEW TO JSJG;
GRANT CREATE DATABASE LINK TO JS_DEV;
GRANT CREATE SYNONYM TO JS_DEV;
GRANT CREATE VIEW TO JS_DEV;
GRANT CREATE DATABASE LINK TO JS_DEV_OPER;
GRANT CREATE SYNONYM TO JS_DEV_OPER;
GRANT CREATE VIEW TO JS_DEV_OPER;
GRANT ALTER SESSION TO JS_TEST;
GRANT CREATE DATABASE LINK TO JS_TEST;
GRANT CREATE MATERIALIZED VIEW TO JS_TEST;
GRANT CREATE SYNONYM TO JS_TEST;
GRANT CREATE VIEW TO JS_TEST;
GRANT CREATE VIEW TO PMSADM;
GRANT CREATE DATABASE LINK TO ZHIFU;
GRANT CREATE SYNONYM TO ZHIFU;
GRANT CREATE VIEW TO ZHIFU;
25 rows selected.
利用上面的授權，就可以在目標資料庫建立使用者，並授權，篇幅所限，這個過程就省略了。
下面就可以執行PULL_TABLESPACES過程了，需要注意，這個過程的輸入引數包括了索引組織表型別和記錄，因此需要透過建立一個PL/SQL匿名塊來完成呼叫，注意輸入引數變數的宣告需要用DBMS_STREAMS_TABLESPACE_ADM包中的型別進行宣告，否則呼叫會報錯：
SQL> DECLARE
2 V_JOB_NAME VARCHAR2(30) := 'MY_IMP_TABLESPACE';
3 V_TABLESPACE_NAME DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
4 V_DIRECTORY_NAME DBMS_STREAMS_TABLESPACE_ADM.DIRECTORY_OBJECT_SET;
5 V_LOGFILE DBMS_STREAMS_TABLESPACE_ADM.FILE;
6 BEGIN
7 V_TABLESPACE_NAME(1) := 'JSGOV';
8 V_TABLESPACE_NAME(2) := 'JSJG';
9 V_TABLESPACE_NAME(3) := 'DEMO_WH_TBS';
10 V_TABLESPACE_NAME(4) := 'JSTBS';
11 V_TABLESPACE_NAME(5) := 'ZHIFU';
12 V_TABLESPACE_NAME(6) := 'JS_TEST';
13 V_DIRECTORY_NAME(1) := 'D_TRANS';
14 V_LOGFILE.DIRECTORY_OBJECT := 'DMP_DIR';
15 V_LOGFILE.FILE_NAME := 'imp_tablespaces.log';
16 DBMS_STREAMS_TABLESPACE_ADM.PULL_TABLESPACES(V_JOB_NAME, 'KAIFA', V_TABLESPACE_NAME, V_DIRECTORY_NAME, V_LOGFILE);
17 END;
18 /
DECLARE
*
ERROR at line 1:
ORA-06512: at "SYS.DBMS_STREAMS_TABLESPACE_ADM", line 2355
ORA-06512: at line 16
表空間遷移過程報錯了，從返回錯誤資訊上看不出任何有意義的東西，檢查一下對應的LOGFILE日誌：
[oracle@yans1 backup]$ more imp_tablespaces.log
Starting "SYS"."MY_IMP_TABLESPACE":
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
ORA-39082: Object type TRIGGER:"JS_TEST"."CAT_REGION_CM_TR" created with compilation warnings
ORA-39082: Object type TRIGGER:"JS_TEST"."CAT_REGION_CM_TR" created with compilation warnings
ORA-39082: Object type TRIGGER:"JS_TEST"."DRG_ORDHITCOMMTEM_ON_NEWHOS" created with compilation warnings
ORA-39082: Object type TRIGGER:"JS_TEST"."DRG_ORDHITCOMMTEM_ON_NEWHOS" created with compilation warnings
ORA-39082: Object type TRIGGER:"JS_TEST"."ORD_HOSPITAL_CATALOG_BEIAN" created with compilation warnings
ORA-39082: Object type TRIGGER:"JS_TEST"."TRG_PRODUCT_SERIAL" created with compilation warnings
ORA-39082: Object type TRIGGER:"JS_TEST"."TRG_PRODUCT_SERIAL" created with compilation warnings
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Job "SYS"."MY_IMP_TABLESPACE" stopped due to fatal error at 16:02:35
顯然，表空間已經載入完成，只是由於個別物件的問題導致了錯誤資訊。在資料庫中檢查表空間資訊：
SQL> SELECT NAME FROM V$TABLESPACE
2 WHERE NAME IN ('JSGOV', 'JSJG', 'DEMO_WH_TBS', 'JSTBS', 'ZHIFU', 'JS_TEST');
NAME
------------------------------
JS_TEST
ZHIFU
JSTBS
DEMO_WH_TBS
JSJG
JSGOV
6 rows selected.
SQL> SELECT OWNER, COUNT(*)
2 FROM DBA_OBJECTS
3 WHERE OWNER IN ('ZHIFU','DEMO_WH','JSJG','JS_DEV_OPER','DEMO_INFO','PMSADM','JSGOV','JS_DEV','JS_TEST')
4 GROUP BY OWNER;
OWNER COUNT(*)
------------------------------ ----------
ZHIFU 13
JS_DEV 1444
PMSADM 30
JSJG 225
DEMO_WH 347
JSGOV 68
JS_DEV_OPER 31
JS_TEST 1572
DEMO_INFO 602
9 rows selected.
SQL> SELECT TABLESPACE_NAME, STATUS
2 FROM DBA_TABLESPACES
3 WHERE TABLESPACE_NAME IN ('JSGOV', 'JSJG', 'DEMO_WH_TBS', 'JSTBS', 'ZHIFU', 'JS_TEST');
TABLESPACE_NAME STATUS
------------------------------ ---------
DEMO_WH_TBS READ ONLY
JSGOV READ ONLY
JSJG READ ONLY
JSTBS READ ONLY
JS_TEST READ ONLY
ZHIFU READ ONLY
6 rows selected.
雖然執行DBMS_STREAMS_TABLESPACE_ADM包的PULL_TABLESPACES過程報錯了，但是遷移表空間的操作已經完成。剩下根據需要決定是否對匯入過程中的錯誤進行額外的處理。
表空間遷移完成後，載入的表空間處於只讀狀態，可以根據具體的需要決定是否將其置為可寫狀態。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-605055/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle中表空間、表、索引的遷移Oracle索引
- table/index/LOBINDEX遷移表空間Index
- MySQL 遷移表空間，備份單表MySql
- Oracle 12cbigfile表空間物件遷移Oracle物件
- mysql共享表空間擴容,收縮,遷移MySql
- 表空間利用率及表空間的補充
- 用傳輸表空間跨平臺遷移資料
- oracle 表移動表空間Oracle
- Oracle表移動表空間Oracle
- 二維網格的遷移（java實現）Java
- 達夢（DM）資料庫的表空間建立和遷移維護資料庫
- mysql Innodb表空間解除安裝、遷移、裝載的使用方法MySql
- Laravel5.5執行表遷移命令出現表為空的解決方案Laravel
- oracle sql 表空間利用率OracleSQL
- 【資料遷移】XTTS跨平臺傳輸表空間v4TTS
- 【MOS】如何利用RMAN可傳輸表空間遷移資料庫到不同位元組序的平臺(文件 ID 1983639.1)資料庫
- 對Oracle分割槽表進行表空間遷移並處理ORA-14511問題Oracle
- 當使用者無限制使用表空間配額且表空間有足夠空間時出現超出表空間的空間限額
- Oracle資料庫遷移 - 異構傳輸表空間TTS HP-UX遷移至Redhat Linux 7.7Oracle資料庫TTSRedhatLinux
- 移動分割槽表和分割槽索引的表空間索引
- 【資料遷移】XTTS跨平臺傳輸表空間（1.傳統方式）TTS
- 達夢資料庫系統表空間資料檔案遷移過程資料庫
- 16、表空間 建立表空間
- 利用魯棒控制實現深度強化學習駕駛策略的遷移強化學習
- impala 資料表在叢集間遷移方案
- 實戰程式碼（二）:Springboot Batch實現定時資料遷移Spring BootBAT
- 【資料遷移】XTTS跨平臺傳輸表空間v3（3.DFT方式）TTS
- 【資料遷移】XTTS跨平臺傳輸表空間v3（2.RMAN增量）TTS
- 高效利用佇列的空間佇列
- 核間遷移的影響
- KingbaseES的表空間
- PostgreSQL在不同的表空間移動資料檔案SQL
- 表空間TSPITR恢復-實驗
- 【Oracle 恢復表空間】 實驗Oracle
- 【TABLESPACE】Oracle表空間最佳實踐Oracle
- oracle表空間的整理Oracle
- 利用rman copy的方法實現儲存上裸裝置資料檔案的遷移ITPUB
- Oracle表空間Oracle
- oracle 表空間Oracle