数据库迁移中exp导出和imp 导入的使用操作，下面以表空间迁移为例子进行演示，其他以全库，用户操作都一样。 环境准备 1.1 检查需要迁移的数据库是否开启归档功能（没有开启归档功能是无
数据库迁移中exp导出和imp 导入的使用操作，下面以表空间迁移为例子进行演示，其他以全库，用户操作都一样。
- 环境准备
1.1 检查需要迁移的数据库是否开启归档功能（没有开启归档功能是无法迁移的，但是现实中一般都是打开归档的）
[oracle@myyun1 admin]$ export ORACLE_SID=myyun1[oracle@myyun1 admin]$ sqlplus / as sysdbaSQL> archive log list;Database log mode No Archive ModeAutomatic archival DisabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 59Current log sequence 61# 上面 No Archive Mode 现实为打开归档功能1.2 未打开归档功能需要数据库启动到mount 模式下，打开归档功能
SQL> startup mount force;ORACLE instance started.Total System Global Area 588746752 bytesFixed Size 2230592 bytesVariable Size 360711872 bytesDatabase Buffers 222298112 bytesRedo Buffers 3506176 bytesDatabase mounted.# 数据库已经启动到mount 模式下SQL> alter database archivelog;alter database archivelog*ERROR at line 1:ORA-00265: instance recovery required, cannot set ARCHIVELOG mode# 打开归档报错，这个时候不要慌，重新关闭数据库在用startup 启动，再关闭，在启动到mount状态下应该就可以了SQL> alter database archivelog;Database altered.# 打开归档成功SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 60Next log sequence to archive 62Current log sequence 62SQL> alter database open;Database altered.1.3 创建表空间test2，创建用户user2该用户默认的表空间设置我test2
SQL> create tablespace test2 datafile '/data/vdb/u01/product/oracle/oradata/myyun1/test2.dbf' size 50M autoextend on ;Tablespace created.# 表空间test2 已经创建成功SQL> create user user2 identified by 123456 default tablespace test2;User created.# 用户user2 已经创建成功SQL> grant connect,resource to user2;Grant succeeded.1.4 用上面创建的用户user2 登录并建表插入数据
SQL> conn user2/123456Connected.QL> create table tmp_1 as select 1 a,2 b from dual;Table created.SQL> select * from tmp_1; A B---------- ---------- 1 2SQL> select username,default_tablespace from user_users;USERNAME DEFAULT_TABLESPACE------------------------------ ------------------------------USER2 TEST21.5 设置表空间test2为只读
SQL> select tablespace_name,status from user_tablespaces;TABLESPACE_NAME STATUS------------------------------ ---------SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUSERS ONLINETEST READ ONLYTEST2 ONLINE# 当前表空间test2 为onlineSQL> alter tablespace test2 read only;Tablespace altered.# 修改我只读SQL> select tablespace_name,status from user_tablespaces;TABLESPACE_NAME STATUS------------------------------ ---------SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUSERS ONLINETEST READ ONLYTEST2 READ ONLY7 rows selected.# 表空间test2 已经修改为只读模式了。1.6 用exp 工具导出表空间test2结构
[oracle@myyun1 admin]$ exp /'/ as sysdba/' file = '/tmp/test2.dump' transport_tablespace=y tablespaces=test2;Export: Release 11.2.0.3.0 - Production on Mon Dec 12 22:09:21 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in US7ASCII character set and UTF8 NCHAR character setserver uses AL32UTF8 character set (possible charset conversion)Note: table data (rows) will not be exportedAbout to export transportable tablespace metadata...For tablespace TEST2 .... exporting cluster definitions. exporting table definitions. . exporting table TMP_1. exporting referential integrity constraints. exporting triggers. end transportable tablespace metadata exportExport terminated successfully without warnings.# 表空间test2 已经导出，可以看到导出了表tmp_1,完全正确没有报错。1.7 登录目标数据库用imp进行导入
先找到上面导入表空间test2 的结构文件/tmp/test2.dump 和表空间的数据文件
[oracle@myyun1 admin]$ ll /tmp/test2.dump -rw-r--r-- 1 oracle dba 16384 12月 12 22:09 /tmp/test2.dump[oracle@myyun1 admin]$ ll /data/vdb/u01/product/oracle/oradata/myyun1/test2.dbf -rw-r----- 1 oracle dba 52436992 12月 12 22:06 /data/vdb/u01/product/oracle/oradata/myyun1/test2.dbf# 导出的表空间结构文件和表空间数据文件都找到# 导入目的数据库的时候需要目的数据库也有一个相同的用户user2，先登录目的数据库新建用户user2oracle@myyun1 admin]$ export ORACLE_SID=base2SQL> create user user2 identified by 123456;User created.# 用户新建成功正式导入表空间test2 和数据文件中的数据
[oracle@myyun1 admin]$ imp /'/ as sysdba/' file = '/tmp/test2.dump' transport_tablespace=y tablespaces=test2 datafiles='/data/vdb/u01/product/oracle/oradata/myyun1/test2.dbf';Import: Release 11.2.0.3.0 - Production on Mon Dec 12 22:18:45 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.02.00 via conventional pathAbout to import transportable tablespace(s) metadata...import done in US7ASCII character set and AL16UTF16 NCHAR character setimport server uses AL32UTF8 character set (possible charset conversion)export server uses UTF8 NCHAR character set (possible ncharset conversion). importing SYS's objects into SYS. importing SYS's objects into SYS. importing USER2's objects into USER2. . importing table "TMP_1". importing SYS's objects into SYSImport terminated successfully without warnings.# 导入成功登录目的数据库进行检查
SQL> select table_name from user_tables;TABLE_NAME------------------------------TMP_1SQL> select * from tmp_1; A B---------- ---------- 1 2# 检查无误1.8 源表空间test2 修改我online
