1 Be aware of the following limitations as you plan for transportable tablespace use:

1:The source and target database must be on the same hardware platform. For example, you can transport tablespaces between Sun Solaris Oracle databases, or you can transport tablespaces between Windows NT Oracle databases. However, you cannot transport a tablespace from a Sun Solaris Oracle database to an Windows NT Oracle database.
2:The source and target database must use the same character set and national character set.
3:You cannot transport a tablespace to a target database in which a tablespace with the same name already exists.
4:Transportable tablespaces do not support: Materialized, views/replication ,Function-based indexes, Scoped REFs
5:8.0-compatible advanced queues with multiple recipients
SQL> show parameter COMPATIBLE;

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
compatible string
9.2.0.0.0

2 建立Self-Contained Set of Tablespaces

[@more@]

1 Be aware of the following limitations as you plan for transportable tablespace use:

1:The source and target database must be on the same hardware platform. For example, you can transport tablespaces between Sun Solaris Oracle databases, or you can transport tablespaces between Windows NT Oracle databases. However, you cannot transport a tablespace from a Sun Solaris Oracle database to an Windows NT Oracle database.
2:The source and target database must use the same character set and national character set.
3:You cannot transport a tablespace to a target database in which a tablespace with the same name already exists.
4:Transportable tablespaces do not support: Materialized, views/replication ,Function-based indexes, Scoped REFs
5:8.0-compatible advanced queues with multiple recipients
SQL> show parameter COMPATIBLE;

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
compatible string
9.2.0.0.0

2 建立Self-Contained Set of Tablespaces

SQL> edit
已寫入檔案 afiedt.buf

1 create tablespace tsport1
2 datafile 'E:oracleoradataliyongtemptsport1.dbf' size 5m
3* autoextend on next 2m
SQL> /

表空間已建立。

SQL> edit
已寫入檔案 afiedt.buf

1 create tablespace tsport2
2 datafile 'E:oracleoradataliyongtemptsport2.dbf' size 5m
3* autoextend on next 2m
SQL> /

表空間已建立。

SQL> create table tb1 (object_id number,object_name varchar2(10)) tablespace tsp
ort1;

表已建立。

SQL> alter table tb1 add (
2 constraint tb1_pk1 primary key (object_id));

表已更改。
SQL> desc tb1;
名稱是否為空? 型別
----------------------------------------- -------- ----------------------------

OBJECT_ID NOT NULL NUMBER
OBJECT_NAME VARCHAR2(10)

SQL> create table tb2 (object_id number,obj_pro number) tablespace tsport2;

表已建立。

SQL> alter table tb2 add
2 constraint tb2_fk1 foreign key (object_id)
3 references tb1(object_id);

表已更改。
SQL> edit
已寫入檔案 afiedt.buf

1 begin
2      for i in 1..1000 loop
3 insert into tb1 values (i,'Good Job');
4      end loop;
5       commit;
6* end;
SQL> /

PL/SQL 過程已成功完成。
SQL> insert into tb2
2 select object_id,1 from tb1;

已建立1000行。

SQL> conn / as sysdba;

SQL> EXECUTE dbms_tts.transport_set_check('tsport1,tsport2',true);

PL/SQL 過程已成功完成。

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

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

Index LIYONG.TB1_PK1 in tablespace SYSTEM enforces primary constriants of table

LIYONG.TB1 in tablespace TSPORT1

3 開始做Transportable Tablespace

SQL> alter tablespace tsport1 read only;

表空間已更改。

SQL> alter tablespace tsport2 read only;

表空間已更改。
D:>EXP TRANSPORT_TABLESPACE=y TABLESPACES=(tsport1,tsport2) TRIGGERS=y CONSTRA
NTS=n GRANTS=n FILE=expdat.dmp

Export: Release 9.2.0.1.0 - Production on 星期五 12月 24 15:04:30 2004

使用者名稱: liyong/xxx as sysdba

連線到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
已匯出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 將不會匯出表資料（行）
注: 將不匯出對錶 / 檢視 / 序列 / 角色的授權
注: 將不會匯出表的約束條件
關於匯出可傳輸的表空間後設資料...
用於表空間 TSPORT1...
. 正在匯出群集定義
. 正在匯出表定義
. . 正在匯出表 TB1
用於表空間 TSPORT2...
. 正在匯出群集定義
. 正在匯出表定義
. . 正在匯出表 TB2
. 正在匯出觸發器
. 結束匯出可傳輸的表空間後設資料
在沒有警告的情況下成功終止匯出。

注意:
1Although the Export utility is used, only data dictionary structural information (metadata) for the tablespaces is exported. Hence, this operation goes quickly even for a large tablespace.
2If you are performing TSPITR or transport with a strict containment check, use:
EXP TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2)
TTS_FULL_CHECK=Y FILE=expdat.dmp

4 plug tablespace

由於在本地做實驗所以：
SQL> drop tablespace tsport1 including contents cascade constraints;

表空間已丟棄。
SQL> drop tablespace tsport2 including contents cascade constraints;

表空間已丟棄。

D:>IMP TRANSPORT_TABLESPACE=y FILE=expdat.dmp DATAFILES=('E:oracleoradataliy
ongtempTSPORT3.DBF','E:oracleoradataliyongtempTSPORT4.DBF') TABLESPACES=(
tsport1,tsport2)

Import: Release 9.2.0.1.0 - Production on 星期五 12月 24 15:21:32 2004

使用者名稱: liyong/xxx as sysdba

連線到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

經由常規路徑匯出由EXPORT:V09.02.00建立的檔案
關於匯入可傳輸表空間後設資料...
已經完成ZHS16GBK字符集和AL16UTF16 NCHAR 字符集中的匯入
. 正在將SYS的物件匯入到 SYS
. 正在將LIYONG的物件匯入到 LIYONG
. . 正在匯入表 "TB1"
. . 正在匯入表 "TB2"
成功終止匯入，但出現警告。

SQL> conn liyong/xxx
已連線。
SQL> select count(*) from tb2;

COUNT(*)
----------
1000

SQL> select count(*) from tb1;

COUNT(*)
----------
1000

SQL> alter tablespace tsport1 read write;

表空間已更改。

SQL> alter tablespace tsport2 read write;

表空間已更改。

SQL> select owner,object_name,status from dba_objects where object_name = 'TB1';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------

STATUS
-------
LIYONG
TB1
VALID

出現警告應當是由於在imp的時候沒有指定
TTS_OWNERS lists all users who own data in the tablespace set.

imp前確保source DB and target DB的schema相同

出現警告應當是由於在imp的時候沒有指定
TTS_OWNERS lists all users who own data in the tablespace set.

Transporting Tablespaces with Self-Contained

相關文章