1、准备待搬移的表空间TEST
(以下操作SID=ORCL10库上执行)源库
创建表空间TEST
SQL> create tablespace test
  2  datafile 'D:\oracle\product\10.1.0\oradata\orcl10\test01.dbf' size 50m;
Tablespace created
创建用户zy
SQL> create user zy          //记住此用户,后面导入impdp要用到
  2  identified by zouyang
  3  default tablespace test;
User created
为用户赋权:
SQL> grant dba to zy;
Grant succeeded
以新建用户登录,创建数据表:
SQL> conn zy/zouyang@orcl10
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as zy
 
SQL> create table tt
  2  as
  3  select * from dba_tables;
Table created
 
2、确定自包含表空间集合
(以下操作SID=ORCL10库上执行)源库
SQL> execute sys.dbms_tts.transport_set_check('test',true)
PL/SQL procedure successfully completed
经分析没有返回任何信息,说明该表空间test是自包含的:
SQL> select * from sys.transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
 
3、生成要搬移的表空间集合(转储文件)
(以下操作SID=ORCL10库上执行)源库
慰劳生成要搬移的表空间集合,必须首先将所有要搬移的表空间转变为只读状态:
SQL> alter tablespace test read only;
Tablespace altered
当使用EXPDP工具时,转储文件只能被存放在DIRECTORY对象所对应的OS目录中,必须首先建立DIRECTORY对象:
SQL> conn system/e2000jl@orcl10
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as system
 
SQL> create directory dump_dir as 'D:\dump';
Directory created
 
SQL> grant read,write on directory dump_dir to zy;
Grant succeeded
生成要搬移的表空间集合:
C:\Documents and Settings\Administrator>expdp zy/zouyang@orcl10 directory=dump_d
ir dumpfile=transport.dmp transport_tablespaces=test
 
Export: Release 10.1.0.2.0 - Production on 星期三, 25 11, 2009 11:17
Copyright (c) 2003, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "ZY.SYS_EXPORT_TRANSPORTABLE_01"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 701
ORA-01647: tablespace 'TEST' is read only, cannot allocate space in it
报错!
原因:搬移表空间testzy用户的缺省表空间,所以不能以zy用户登录执行expdp
 
C:\Documents and Settings\Administrator>expdp system/e2000jl@orcl10 directory=du
mp_dir dumpfile=transport.dmp transport_tablespaces=test
 
Export: Release 10.1.0.2.0 - Production on 星期三, 25 11, 2009 11:21
Copyright (c) 2003, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/********@orcl10 directo
ry=dump_dir dumpfile=transport.dmp transport_tablespaces=test
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
 
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  D:\DUMP\TRANSPORT.DMP
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 11:22
 
4、传送转储文件和数据文件到目标数据库
(以下操作SID=ORCL库上执行)目标库
U盘拷贝文件 TRANSPORT.DMP(转储文件,存放到:D:\dump\TRANSPORT.DMP)、TEST01.DBF(数据文件,存放到:E:\oracle\product\10.1.0\oradata\orcl\TEST01.DBF
 
5、 插入表空间到目标数据库
确定源数据库与目标数据库数据块尺寸一致:
Sid=orcl10
SQL> show parameter db_block_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
Sid=orcl
SQL> show parameter db_block_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
 
(以下操作SID=ORCL库上执行)目标库
创建接收用户:
SQL> create user zouy
  2  identified by zouyang;
User created
当使用IMPDP工具时,转储文件只能被存放在DIRECTORY对象所对应的OS目录中,必须首先建立DIRECTORY对象:
SQL> conn system/e2000jl@orcl10
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as system
 
SQL> create directory dump_dir as 'D:\dump';
Directory created
 
SQL> grant read,write on directory dump_dir to zouy;
Grant succeeded
插入表空间到目标数据库:
C:\Documents and Settings\zouyang>impdp system/system@orcl directory=dump_dir du
mpfile=TRANSPORT.DMP transport_datafiles=E:\oracle\product\10.1.0\oradata\orcl\T
EST01.DBF remap_schema=zy:zouy
Import: Release 10.1.0.2.0 - Production on 星期三, 25 11, 2009 13:50
Copyright (c) 2003, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@orcl directory
=dump_dir dumpfile=TRANSPORT.DMP transport_datafiles=E:\oracle\product\10.1.0\or
adata\orcl\TEST01.DBF remap_schema=zy:zouy
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 13:50
 
6、将新导入的表空间回复读写状态
SQL> select TABLESPACE_NAME,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
----------------------------------------------- ----------------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                            ONLINE
USERS                           ONLINE
TEST                           READ ONLY    ——已成功插入,不过还是只读状态
6 rows selected
 
SQL> conn zouy/zouyang@orcl
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as zouy
 
SQL> select count(*) from tt;   ——test表空间的数据也一起成功插入
  COUNT(*)
----------
      3093
回复迁移的表空间致读写状态:
SQL> alter tablespace test read write;
Tablespace altered