在DB130上先建两个tablespace,分别存放data与index.
SQL> create tablespace eric_data
2 datafile '/oracle/oradata/orcl/eric_data01.dbf' size 50m;
Tablespace created.
SQL> create tablespace eric_indx
2 datafile '/oracle/oradata/orcl/eric_indx01.dbf' size 10m;
Tablespace created.
建立测试前的用户及table,index
SQL> grant connect,resource to eric identified by eric;
Grant succeeded.
SQL> alter user eric default tablespace eric_data;
User altered.
SQL> conn eric/eric
Connected.
SQL> create table eric as select rownum id,dummy from dual;
Table created.
SQL> create index ind_eric on eric(id) tablespace eric_indx;
Index created.
用sys用户来执行非严格包括检查(full_check=false)
SQL> conn / as sysdba
Connected.
SQL> exec dbms_tts.transport_set_check('ERIC_DATA',true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
下面执行自包含检查(full_check=true)
SQL> exec dbms_tts.transport_set_check('ERIC_DATA',true,true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Index ERIC.IND_ERIC in tablespace ERIC_INDX points to table ERIC.ERIC in tablesp
ace ERIC_DATA
反过来对于eric_indx表空间来说，非严格检查也是无法通过的
SQL> exec dbms_tts.transport_set_check('ERIC_INDX',true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Index ERIC.IND_ERIC in tablespace ERIC_INDX points to table ERIC.ERIC in tablesp
ace ERIC_DATA
但可以对多个表空间同时传输，就不会有问题了：
SQL> exec dbms_tts.transport_set_check('ERIC_DATA,ERIC_INDX',true,true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
现在开始将DB130上的ERIC_DATA与ERIC_INDX同时传输到DB131上。
测试一：只传输eric_data，而不传输eric_indx,看是否能成功
1）将表空间设置为只读：
DB130：
SQL> alter tablespace eric_data read only;
Tablespace altered.
[oracle@linx130 admin]$ exp '/ as sysdba' tablespaces=eric_data transport_tablespace=y file=/home/oracle/exp_ericdata.dmp
.
Export: Release 10.2.0.3.0 - Production on Sun Jan 13 20:39:14 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace ERIC_DATA ...
. exporting cluster definitions
. exporting table definitions
. . exporting table ERIC
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
DB131：
将dmp文件与dbf文件用binary的方式传输过来
[oracle@linx131 orcl]$ ftp 172.17.61.130
Connected to 172.17.61.130.
Name (172.17.61.130:oracle): oracle
331 Please specify the password.
Password:
230 Login successful.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> bin
200 Switching to Binary mode.
ftp> mget exp_ericdata.dmp
mget exp_ericdata.dmp? y
227 Entering Passive Mode (172,17,61,130,244,103)
150 Opening BINARY mode data connection for exp_ericdata.dmp (16384 bytes).
226 File send OK.
16384 bytes received in 0.0083 seconds (1.9e+03 Kbytes/s)
ftp> cd /oracle/oradata/orcl/
250 Directory successfully changed.
ftp> mget eric_data01.dbf
mget eric_data01.dbf? y
227 Entering Passive Mode (172,17,61,130,176,43)
150 Opening BINARY mode data connection for eric_data01.dbf (52436992 bytes).
226 File send OK.
52436992 bytes received in 6 seconds (8.5e+03 Kbytes/s)
在imp之前先建立eric的用户
[oracle@linx131 ~]$ sqlplus / as sysdba
SQL> grant connect,resource to eric identified by eric;
Grant succeeded.
[oracle@linx131 ~]$ imp '/ as sysdba' tablespaces=eric_data transport_tablespace=y file=exp_ericdata.dmp datafiles=eric_data01.dbf
Import: Release 10.2.0.3.0 - Production on Sun Jan 13 20:57:25 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 1565:
"BEGIN sys.dbms_plugts.beginImpTablespace('ERIC_DATA',6,'SYS',1,0,8192,1,5"
"35392,1,2147483645,8,128,8,0,1,0,8,1171353837,1,33,534350,NULL,0,0,NULL,NUL"
"L); END;"
IMP-00003: ORACLE error 1565 encountered
ORA-01565: error in identifying file 'eric_data01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.DBMS_PLUGTS", line 1801
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
没有成功，经多次测试，原因是文件没有指定绝对路径，指定后OK。
[oracle@linx131 ~]$ imp '/ as sysdba' tablespaces='eric_data' transport_tablespace=y file='/home/oracle/exp_ericdata.dmp' datafiles='/home/oracle/eric_data01.dbf'
Import: Release 10.2.0.3.0 - Production on Sun Jan 13 21:35:45 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing ERIC's objects into ERIC
. . importing table "ERIC"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
下面再来测试eric_data与eric_indx两个表空间同时传输。
将eric_indx也只主读
DB130：
SQL> alter tablespace eric_indx read only;
Tablespace altered.
将eric_data与eric_indx汇出来
[oracle@linx130 orcl]$ exp '/ as sysdba' tablespaces=eric_data,eric_indx transport_tablespace=y file=/home/oracle/exp_ericdata_ericindx.dmp
Export: Release 10.2.0.3.0 - Production on Sun Jan 13 21:03:58 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace ERIC_DATA ...
. exporting cluster definitions
. exporting table definitions
. . exporting table ERIC
EXP-00091: Exporting questionable statistics.
For tablespace ERIC_INDX ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully with warnings.
DB131：
[oracle@linx131 ~]$ !ftp
ftp 172.17.61.130
Connected to 172.17.61.130.
220 (vsFTPd 2.0.1)
KERBEROS_V4 rejected as an authentication type
Name (172.17.61.130:oracle): oracle
331 Please specify the password.
Password:
230 Login successful.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> mget exp_ericdata_ericindx.dmp
mget exp_ericdata_ericindx.dmp? y
227 Entering Passive Mode (172,17,61,130,209,11)
150 Opening BINARY mode data connection for exp_ericdata_ericindx.dmp (16384 bytes).
226 File send OK.
16384 bytes received in 0.0079 seconds (2e+03 Kbytes/s)
ftp> cd /oracle/oradata/orcl
250 Directory successfully changed.
ftp> mget eric_indx01.dbf
mget eric_indx01.dbf? y
227 Entering Passive Mode (172,17,61,130,21,243)
150 Opening BINARY mode data connection for eric_indx01.dbf (10493952 bytes).
226 File send OK.
10493952 bytes received in 1.3 seconds (8.1e+03 Kbytes/s)
再imp进来，OK
[oracle@linx131 ~]$ imp '/ as sysdba' tablespaces='eric_data','eric_indx' transport_tablespace=y file='/home/oracle/exp_ericdata_ericindx.dmp' datafiles='/oracle/oradata/orcl/eric_data01.dbf','/oracle/oradata/orcl/eric_indx01.dbf'
Import: Release 10.2.0.3.0 - Production on Sun Jan 13 21:30:22 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing ERIC's objects into ERIC
. . importing table "ERIC"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
[oracle@linx131 ~]$
最后将表空间改为read wirte
DB130:
SQL> alter tablespace eric_data read write;
Tablespace altered.
SQL> alter tablespace eric_indx read write;
Tablespace altered.
DB131:
SQL> alter tablespace eric_data read write;
Tablespace altered.
SQL> alter tablespace eric_indx read write;
Tablespace altered.