兩隻小虎,一隻豬

Oracle 進行資料移轉的時候,通常會使用export/import來進行,但是當需要移轉大量資料時,
使用export/import 就會顯得非常沒有效率Oracle 提供了TRANSPORT_TABLESPACE 的方式來加快資料移轉的效率,其原理是

1.將Tablespace內的物件定義利用export 指令匯出
2.將資料檔用複製的方式,複製一份到另一個DB上
3.將export 出的物件定義,透過import 的指令匯入到另一個DB

TestDB1

1. Creaete TP_TEST tablespace on testdb1

SQL> create tablespace TP_TEST datafile '/data1/testdb1/tp_test.dbf' size 1m;

Tablespace created.

2. Create u1 user and grant privilege

SQL> create user u1 identified by manager default tablespace TP_TEST;

User created.

SQL> grant connect , resource , dba to u1;

Grant succeeded.

3. Create test table on u1 schema, and insert value into test table

SQL> create table u1.table_test(id int) tablespace TP_TEST;

Table created.

SQL>insert into u1.table_test values(1);

1 row created. 
SQL>commit;

Commit complete.
 
SQL> select * from u1.table_test;

        ID
----------
         1

4. execute dbsm_tts.transport_set_check procedure

SQL> exec sys.dbms_tts.transport_set_check('TP_TEST' , true);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

查詢transport_set_violations沒有任何row 回傳,表示可以進行transport tablespace, 如果有任何訊息則需要進一步的處理,例如下面select 出的結果表示無法進行transport tablespace

SQL> select * from TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
--------------------------------------------------------------------------------
Sys owned object  TABLE_TEST in tablespace TP_TEST not allowed in pluggable set

SQL> select * from TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
--------------------------------------------------------------------------------

Constraint FK_T between table XYS.TT in tablespace DMT and table TEST1.T in tablespace TBS16

5. transport tablespace and copy datafile to testdb2

SQL>show user

USER is "SYS"

SQL>alter tablespace TP_TEST read only;
 
SQL> host exp transport_tablespace=y tablespaces=TP_TEST file=/data1/testdb1/tts_tp_test.dmp;

Export: Release 9.2.0.8.0 - Production on Wed May 25 15:03:01 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Username: / as sysdba

Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Export done in UTF8 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TP_TEST ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                     TABLE_TEST
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

SQL> host ls -l /data1/testdb1/tts_tp_test.dmp
-rw-r--r--  1 oracle dba 16384 May 25 15:03 /data1/testdb1/tts_tp_test.dmp

[oracle@tpplmdbt01 ~]$ cp /data1/testdb1/tp_test.dbf /data1/testdb2


SQL> alter tablespace TP_TEST read write ;

Tablespace altered.

TESTDB2

SQL> create user u1 identified by manager;

User created.
SQL>  grant connect , resource , dba to u1;

Grant succeeded.

SQL>host imp transport_tablespace=y datafiles='/data1/testdb2/tp_test.dbf' tablespaces=TP_TEST file=/data1/testdb1/tts_tp_test.dmp

Import: Release 9.2.0.8.0 - Production on Wed May 25 15:15:34 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Username: / as sysdba

Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing U1's objects into U1
. . importing table                   "TABLE_TEST"
Import terminated successfully without warnings.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP 
TP_TEST

SQL> select * from u1.table_test;

        ID
----------
         1

復原testdb1, testdb2 環境

SQL> drop table u1.table_test  ;
Table dropped.

SQL> drop user u1;
User dropped.

SQL> drop tablespace TP_TEST including contents and datafiles;
Tablespace dropped.