表空间的传输

假如你想从A库把USERS表空间传到B库：

在A库上：

1、alter tablespace users read only;

2、exp \'sys/sys as sysdba\' tablespaces=users transport_tablespace=y file=/bk/users.dmp;

3、cp /u01/app/oracle/oradata/testyd/users01.dbf /bk/users01.dbf

然后把users.dmp和users01.dbf拷贝到B库

在B库上：

1、 imp \'sys/sys as sysdba\' tablespaces=users transport_tablespace=y file=/bk/users.dmp datafiles=/bk/users.dbf

2、alter tablespace users read write;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

需要注意的问题:

1、cp出来的users01.dbf要考虑是否需要转字符序（convert）

2、不相同的数据库字符集和国家字符集是不能完成表空间迁移的！报错如下，要多加注意

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export server uses UTF8 NCHAR character set (possible ncharset conversion)
IMP-00017: following statement failed with ORACLE error 29345:
 "BEGIN   sys.dbms_plugts.beginImport ('10.2.0.3.0',873,'871',13,'Linux 64-bi"
 "t for AMD',12006,39801,1,0,0,0); END;"
IMP-00003: ORACLE error 29345 encountered
ORA-29345: cannot plug a tablespace into a database using an incompatible character set
ORA-06512: at "SYS.DBMS_PLUGTS", line 2386
ORA-06512: at "SYS.DBMS_PLUGTS", line 1946
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully

3、user表空间没有read only导出dmp和复制数据文件会报如下错误

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.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 US7ASCII character set and AL16UTF16 NCHAR character set

import server uses UTF8 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 19722:

  "BEGIN    sys.dbms_plugts.checkDatafile(NULL,4007413368,5,1280,11,5,0,0,25024"

  "0,305817,1,20971522,NULL,NULL,NULL); END;"

IMP-00003: ORACLE error 19722 encountered

ORA-19722:  datafile /home/oracle/user.dbf is an incorrect version

ORA-06512: at "SYS.DBMS_PLUGTS", line 2065

ORA-06512: at line 1

IMP-00000: Import terminated unsuccessfully

4、SCP复制的用的是root用户,导致属组和属主是ROOT,oracle没权限访问,导入如下错误

Export file created by EXPORT:V10.02.01 via conventional path

About to import transportable tablespace(s) metadata...

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses UTF8 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('USERS',11,'SYS',1,0,8192,1,3058"

  "17,1,2147483645,8,128,8,0,1,0,8,4007413368,1,33,250233,NULL,0,0,NULL,NULL);"

  " END;"

IMP-00003: ORACLE error 1565 encountered

ORA-01565: error in identifying file '/home/oracle/user.dbf'

ORA-27041: unable to open file

Linux Error: 13: Permission denied

Additional information: 2

ORA-06512: at "SYS.DBMS_PLUGTS", line 1801

ORA-06512: at line 1

IMP-00000: Import terminated unsuccessfully