EXPDP&IMPDP搬移表空间-笔记

http://407882.blog.51cto.com/397882/235072

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

报错！

原因：搬移表空间 test 是 zy 用户的缺省表空间，所以不能以 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