imp oracle reschema_Oracle数据库逻辑备份之exp/imp(一)

Exp/imp工具已经比较古老了，由于它是一个客户端工具，可以直接连接数据库把数据导出到客户端。对于少量数据的备份，它仍是一个不错的工具。Exp/imp使用帮助如下：

C:\Users\HuangXing>exp help=yExport: Release 11.2.0.1.0 - Production on 星期二 8月 13 19:59:32 2013通过输入 EXP 命令和您的用户名/口令, 导出操作将提示您输入参数:

例如: EXP SCOTT/TIGER

或者, 您也可以通过输入跟有各种参数的 EXP 命令来控制导出的运行方式。要指定参数, 您可以使用关键字:

格式:  EXP KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)例如: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)或 TABLES=(T1:P1,T1:P2), 如果 T1 是分区表

USERID 必须是命令行中的第一个参数。

关键字   说明 (默认值)         关键字      说明 (默认值)--------------------------------------------------------------------------USERID   用户名/口令           FULL        导出整个文件 (N)BUFFER   数据缓冲区大小        OWNER        所有者用户名列表FILE     输出文件 (EXPDAT.DMP)  TABLES     表名列表COMPRESS  导入到一个区 (Y)   RECORDLENGTH   IO 记录的长度GRANTS    导出权限 (Y)          INCTYPE     增量导出类型INDEXES   导出索引 (Y)         RECORD       跟踪增量导出 (Y)DIRECT    直接路径 (N)         TRIGGERS     导出触发器 (Y)LOG      屏幕输出的日志文件    STATISTICS    分析对象 (ESTIMATE)ROWS      导出数据行 (Y)        PARFILE      参数文件名CONSISTENT 交叉表的一致性 (N)   CONSTRAINTS  导出的约束条件 (Y)

OBJECT_CONSISTENT    只在对象导出期间设置为只读的事务处理 (N)FEEDBACK             每 x 行显示进度 (0)FILESIZE             每个转储文件的最大大小FLASHBACK_SCN        用于将会话快照设置回以前状态的 SCNFLASHBACK_TIME       用于获取最接近指定时间的 SCN 的时间QUERY                用于导出表的子集的 select 子句RESUMABLE            遇到与空格相关的错误时挂起 (N)RESUMABLE_NAME       用于标识可恢复语句的文本字符串RESUMABLE_TIMEOUT    RESUMABLE 的等待时间TTS_FULL_CHECK       对 TTS 执行完整或部分相关性检查TABLESPACES          要导出的表空间列表TRANSPORT_TABLESPACE 导出可传输的表空间元数据 (N)TEMPLATE             调用 iAS 模式导出的模板名

成功终止导出, 没有出现警告。

1 字符集设置

在使用exp工具导出数据时，为了防止数据转换导致乱码的出现，需要把客户端字符集设置成和数据库字符集一致：

查看数据库字符集：

SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

PARAMETER            VALUE-------------------- --------------------NLS_CHARACTERSET     ZHS16GBK

设置客户端字符集，和源库一样：

C:\Users\HX>set nls_lang="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

导入时候，导入客户端字符集也要设置成和导出字符集一样，这样即使目的库和源库字符集不一致，字符转换也只是发生在目的库上：

C:\Users\HX>set nls_lang="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

2 单表导入导出

2.1查询导出

现在，需要把system用户的表student导出，而且只需导出age !=50 的记录。

在windows客户端，可以新建一个bat文件student.bat，包含内容如下：

@echo offsqlplus system/orcl@localhost/orcljjyf2 @student.sql

文件student.sql内容如下：

column time new_val okselect to_char(sysdate,'yyyy-mm-dd') time from dual;host exp "'system/orcl@localhost/orcljjyf2'" file=stuent_&ok..dmp tables=student% query='where age "!=" 50'  log=student_&ok..logexit

这样，导出的文件就可以根据时间命名了。

执行student.batSQL*Plus: Release 10.2.0.4.0 - Production on 星期二 8月 13 21:14:28 2013Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsTIME----------2013-08-13Export: Release 10.2.0.4.0 - Production on 星期二 8月 13 21:14:28 2013Copyright (c) 1982, 2007, Oracle.  All rights reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径.... . 正在导出表                         STUDENT导出了           4 行EXP-00091: 正在导出有问题的统计信息。EXP-00091: 正在导出有问题的统计信息。导出成功终止, 但出现警告。SQL>

如果在linux上，直接用下面语句就可以了：

oracle[~/mysql]$exp  'hr/hr@localhost:1523/hexel'  file=stuent_`date +%Y%m%d_%H:%M:%S`.dmp tables=jobs% query=\"where MIN_SALARY \!\= 3000\"  log=student_`date +%Y%m%d_%H:%M:%S`.log

可以把上面的导出语句写入一个参数文件：

oracle[~/mysql]$cat jobs.lstuserid="hr/talent123@localhost:1523/hexel"file=stuent_`date +%Y%m%d_%H:%M:%S`.dmptables=jobs%query="where MIN_SALARY != 3000"log=student_.log

执行导出：

oracle[~/mysql]$exp parfile=jobs.lst

2.2数据导入

2.2.1 导入用户和导出用户相同

d:\>imp "system/orcl@localhost/orcljjyf2" file=stuent_2013-08-13.dmp tables=student

Import: Release 10.2.0.4.0 - Production on 星期二 8月 13 21:55:36 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

经由常规路径由 EXPORT:V10.02.01 创建的导出文件已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入. 正在将 SYSTEM 的对象导入到 SYSTEM. 正在将 SYSTEM 的对象导入到 SYSTEM. . 正在导入表                       "STUDENT"导入了           4 行成功终止导入, 没有出现警告。d:\>sqlplus system/orcl@localhost/orcljjyf2SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 8月 13 21:55:54 2013Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from student;

ID NAME              AGE GENDE MAJOR---------- ---------- ---------- ----- ----------1234 1234               25 男    1234230 1                 111 男    11110 1234               25 男    12341 1                   1 男    1如果用sys用户导入，由于导出时候student表属主是system，所以会产生警告：

oracle[~]$imp "'sys/talent@localhost:1523/hexel as sysdba'" file=stuent_2013-08-13.dmp tables=student IGNORE=y

Warning: the objects were exported by SYSTEM, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set. importing SYSTEM's objects into SYS. importing SYSTEM's objects into SYS. . importing table                      "STUDENT"          4 rows importedImport terminated successfully without warnings.

2.2.2 导入给不同用户

如果表是由dba用户导出的，那么导入时候只有dba用户能把它表导入给自己，或者导入给其他用户：

例如：现在用sys帐号，把这个表导入给用户hr:

oracle[~]$imp "'sys/talent@localhost:1523/hexel as sysdba'" file=stuent_2013-08-13.dmp tables=student fromuser=system touser=hr

如果用户hr已经有了这个表，那么会报错，例如：

oracle[~/mysql]$imp system/talent file=stuent_2013-08-15.dmp tables=student fromuser=system touser=hr

Export file created by EXPORT:V10.02.01 via conventional pathimport done in ZHS16GBK character set and AL16UTF16 NCHAR character set. importing SYSTEM's objects into HRIMP-00015: following statement failed because the object already exists:"CREATE TABLE "STUDENT" ("ID" NUMBER(5, 0) NOT NULL ENABLE, "NAME" VARCHAR2(""10), "AGE" NUMBER(5, 0), "GENDER" VARCHAR2(5), "MAJOR" VARCHAR2(10))  PCTFR""EE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 ""FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRES""S"Import terminated successfully with warnings.

Exp导出是是导出相关对象的ddl语句和数据，所以如果从高版本的数据往低版本的数据库迁移，由于对象建立时候的选项可能不兼容(例如nocompress属性是9i R2以后才有的选项)，可能会导致迁移失败。

由于oracle exp增量导入不靠谱，最好先重命令原来的表，导入完成后，再把数据导回去：

例如：

HR >alter table student rename to student1;oracle[~/mysql]$imp system/talent file=stuent_2013-08-15.dmp tables=student fromuser=system touser=hrHR >insert into student  select * from student1;当然上面的语句注意是否违反相关约束条件

3 模式导出导入

3.1用户导出自己的所有数据：

oracle[~/mysql]$exp hr/talent123 file=hr_`date +%Y%m%d_%H:%M:%S`.dmp log=hr`date +%Y%m%d_%H:%M:%S`.log

Export: Release 11.2.0.3.0 - Production on 星期四 8月 15 20:59:10 2013

Copyright (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 ZHS16GBK character set and AL16UTF16 NCHAR character set. exporting pre-schema procedural objects and actions. exporting foreign function library names for user HR. exporting PUBLIC type synonyms. exporting private type synonyms. exporting object type definitions for user HRAbout to export HR's objects .... exporting database links. exporting sequence numbers. exporting cluster definitions. about to export HR's tables via Conventional Path .... . exporting table                      COUNTRIES         25 rows exported. . exporting table                    DEPARTMENTS         27 rows exported. . exporting table                      EMPLOYEES        107 rows exported. . exporting table                           JOBS         19 rows exported. . exporting table                    JOB_HISTORY         10 rows exported. . exporting table                      LOCATIONS         23 rows exported. . exporting table                        REGIONS          4 rows exported. . exporting table                        STUDENT          4 rows exported. . exporting table                       STUDENT1          4 rows exported. . exporting table                     TEST_TABLE          1 rows exported. . exporting table               TIME_RANGE_SALES. . exporting partition                     SALES_1998          3 rows exported. . exporting partition                     SALES_1999          1 rows exported. . exporting partition                     SALES_2000          0 rows exported. . exporting partition                     SALES_2001          1 rows exported. exporting synonyms. exporting views. exporting stored procedures. exporting operators. exporting referential integrity constraints. exporting triggers. exporting indextypes. exporting bitmap, functional and extensible indexes. exporting posttables actions. exporting materialized views. exporting snapshot logs. exporting job queues. exporting refresh groups and children. exporting dimensions. exporting post-schema procedural objects and actions. exporting statistics

Export terminated successfully without warnings.

3.2用户导入自己的数据

对于上面导入的全模式文件，如果现在想在另外一个机器全部导入，可以使用下面语句:

oracle[~/mysql]$imp hr/talent123 file=hr_20130815_21:03:41.dmp full=y

如果只想导入一个表，可以用下面语句:

oracle[~/mysql]$imp hr/talent123 file=hr_20130815_21:03:41.dmp tables=student;

3.3导出别人的模式

加上选项owner即可，例如hr用户导出system用户的数据，hr用户需要dba权限：

oracle[~/mysql]$exp hr/talent123  file=hr_`date +%Y%m%d_%H:%M:%S`.dmp log=hr`date +%Y%m%d_%H:%M:%S`.log  owner=system

4 全库导入和导出

4.1全库导出

全库导出实际是导出全部模式的数据，在模式导出基础上加上full=y即可

oracle[~/mysql]$exp hr/talent123  file=hr_`date +%Y%m%d_%H:%M:%S`.dmp log=hr`date +%Y%m%d_%H:%M:%S`.log full=yConnected 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 ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export the entire database .... exporting tablespace definitions. exporting profiles. exporting user definitions. exporting roles. exporting resource costs. exporting rollback segment definitions. exporting database links. exporting sequence numbers. exporting directory aliases. exporting context namespaces. exporting foreign function library names. exporting PUBLIC type synonyms. exporting private type synonyms. exporting object type definitions. exporting system procedural objects and actions. exporting pre-schema procedural objects and actions. exporting cluster definitions. about to export SYSTEM's tables via Conventional Path .... . exporting table                    DEF$_AQCALL          0 rows exported. . exporting table                   DEF$_AQERROR          0 rows exported. . exporting table                  DEF$_CALLDEST          0 rows exported. . exporting table               DEF$_DEFAULTDEST          0 rows exported. . exporting table               DEF$_DESTINATION          0 rows exported. . exporting table                     DEF$_ERROR          0 rows exported. . exporting table                       DEF$_LOB          0 rows exported. . exporting table                    DEF$_ORIGIN          0 rows exported. . exporting table                DEF$_PROPAGATOR          0 rows exported. . exporting table       DEF$_PUSHED_TRANSACTIONS          0 rows exported. . exporting table                      HUANGXING          0 rows exported

全库导出导出了表空间，角色，以及模式相关数据。

4.2全库导入

可以利用全库导出的数据，单独导入某个用户的数据。

例如，现在仅仅想导入hr模式的student表：

oracle[~/mysql]$imp hr/talent123 file=hr_20130815_21:31:54.dmp tables=student只导入hr模式的所有内容：oracle[~/mysql]$imp hr/talent123 file=hr_20130815_21:31:54.dmp fromuser=hr touser=hr ignore=y commit=y全库导入：oracle[~/mysql]$imp hr/talent123 file=hr_20130815_21:31:54.dmp full=y commit=y