Oracle傳輸資料表空間功能測試(含詳細過程)

最近做Oracle資料移轉，之前有一篇遷移思路思考的文章，這裡繼續做具體的測試，主題問資料表空間傳輸。

Oracle 傳輸資料表空間

使用可傳輸資料表空間將Oracle 9i升級到10g 

Oracle 傳輸資料表空間遷移資料總結

exp/expdp傳輸資料表空間和rman convert實現大資料量快速遷移

一、原始伺服器上匯出資料表空間
原始伺服器:  10.1.122.55
目標伺服器：10.1.122.54

0.設定字元集
注意，這裡不設定字元集在匯入的時候會報錯，詳細情況見文章的最後。
SUSE11sp2:~ # export LANG=AMERICAN_AMERICA.AL32UTF8
suse11sp2:~> export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

suse11sp2:~> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 24 14:45:47 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

1.準備需要傳輸的資料表空間
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace aaa datafile '/oracle/oradata/aa.dbf' size 100M ;

Tablespace created.

SQL> CREATE USER aaa  IDENTIFIED BY aaa
DEFAULT TABLESPACE aaa
TEMPORARY TABLESPACE  temp;  2    3

User created.

SQL> GRANT CONNECT,RESOURCE TO aaa;

Grant succeeded.

SQL> REVOKE UNLIMITED TABLESPACE FROM aaa;

Revoke succeeded.

SQL> ALTER USER aaa QUOTA UNLIMITED ON aaa;

User altered.

SQL> conn aaa/aaa;
Connected.
SQL> create table a1(id varchar2(10),name varchar2(20));

Table created.

SQL> insert into a1 values('01','lurou');

1 row created.

SQL> insert into a1 values('02','hello,DBA!');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select * from a1;

ID        NAME
---------- --------------------
01        lurou
02        hello,DBA!

SQL>
SQL>
SQL>

 

2.做傳輸前檢查
SQL> conn / as sysdba
Connected.
SQL>
SQL> execute sys.dbms_tts.transport_set_check('aaa',true);

PL/SQL procedure successfully completed.

SQL>
SQL> select * from sys.transport_set_violations;

no rows selected

SQL>

3.設定資料表空間為唯讀
SQL>
SQL> alter tablespace aaa read only;

Tablespace altered.

SQL>
SQL> commit;

Commit complete.

SQL>

1 2 下一頁