【傳輸資料表空間】使用 EXPDP/IMPDP工具的傳輸資料表空間完成資料移轉

【傳輸資料表空間】使用 EXPDP/IMPDP工具的傳輸資料表空間完成資料移轉

本文通過實踐示範，完整類比一下使用EXPDP/IMPDP工具實現傳輸資料表空間的過程，供參考。
任務描述：將secdb1執行個體上的sec使用者資料表空間tbs_sec的資料轉送到secdb2執行個體上secooler使用者下。

1.secdb1執行個體環境準備
1）建立待操作的資料表空間
sys@secdb1> create tablespace tbs_sec datafile '/u01/app/Oracle/oradata/secdb1/dfile/tbs_sec_01.dbf' size 5 m autoextend on;

Tablespace created.

2）重新建立sec使用者並授權
sys@secdb1> drop user sec cascade;

User dropped.

sys@secdb1> create user sec identified by sec default tablespace tbs_sec;

User created.

sys@secdb1> grant connect,resource to sec;

Grant succeeded.

3）在sec使用者下簡單建立一張表並初始化一條記錄
sys@secdb1> conn sec/sec
Connected.
sec@secdb1> create table t(x int);

Table created.

sec@secdb1> insert into t values (1);

1 row created.

sec@secdb1> commit;

Commit complete.

sec@secdb1> select * from t;

        X
----------
        1

2.檢tbs_sec資料表空間是否“自包含”
sys@secdb1> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tbs_sec',true);

PL/SQL procedure successfully completed.

sys@secdb1> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

OK，沒有問題。

3.將資料表空間調整到唯讀狀態
sys@secdb1> ALTER TABLESPACE tbs_sec read only;

Tablespace altered.

4.使用EXPDP工具完成資料表空間元素據匯出
sys@secdb1> create directory dir_home as '/home/oracle';

Directory created.

sys@secdb1> grant read,write on directory dir_home to public;

Grant succeeded.

[oracle@seclinux ~]$ expdp system/oracle1 directory=dir_home dumpfile=tbs_sec.dmp transport_tablespaces=tbs_sec transport_full_check=y

Export: Release 10.2.0.1.0 - Production on Saturday, 23 July, 2010 22:23:21

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=dir_home dumpfile=tbs_sec.dmp transport_tablespaces=tbs_sec transport_full_check=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/tbs_sec.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 22:23:39

5.將tbs_sec資料表空間對應的資料檔案拷貝到secdb2執行個體對應的目錄
[oracle@seclinux ~]$ cp /u01/app/oracle/oradata/secdb1/dfile/tbs_sec_01.dbf /u01/app/oracle/oradata/secdb2/tbs_sec_01.dbf

6.在secdb2執行個體上使用IMPDP工具完成資料表空間的匯入
sys@secdb2> create user secooler identified by secooler;

User created.

sys@secdb2> grant connect,resource to secooler;

Grant succeeded.

sys@secdb2> create directory dir_home as '/home/oracle';

Directory created.

sys@secdb2> grant read,write on directory dir_home to public;

Grant succeeded.

[oracle@seclinux ~]$ impdp system/oracle1 DUMPFILE=tbs_sec.dmp DIRECTORY=dir_home TRANSPORT_DATAFILES=/u01/app/oracle/oradata/secdb2/tbs_sec_01.dbf REMAP_SCHEMA=(sec:secooler)

Import: Release 10.2.0.1.0 - Production on Saturday, 23 July, 2010 22:34:48

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** DUMPFILE=tbs_sec.dmp DIRECTORY=dir_home TRANSPORT_DATAFILES=/u01/app/oracle/oradata/secdb2/tbs_sec_01.dbf REMAP_SCHEMA=(sec:secooler)
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 22:34:52

7.在secdb2執行個體上驗證資料匯入效果
sys@secdb2> conn secooler/secooler
Connected.
secooler@secdb2> select * from cat;

TABLE_NAME                    TABLE_TYPE
------------------------------ -----------
T                              TABLE

secooler@secdb2> select * from t;

        X
----------
        1

sys@secdb2> select owner,tablespace_name from dba_tables where wner='SECOOLER' and table_name='T';

OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------
SECOOLER                      TBS_SEC

OK，使命完成。

8.最後記得將secdb1執行個體中tbs_sec資料表空間置為可讀寫入模式
sys@secdb1> ALTER TABLESPACE tbs_sec read write;

Tablespace altered.

9.小結
使用EXPDP/IMPDP傳輸資料表空間功能可以比較便捷高效的完成資料移轉，善用之。
有關使用EXP/IMP工具完成傳輸資料表空間的操作方法，請參考如下文章，大同小異。
《【實驗】利用可傳輸資料表空間技術實現資料的高效遷移》（）