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