Oracle数据导入导出imp-exp

2009-03-12 14:04

Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。exp 命令可

以把数据从远程数据库服务器导出到本地的dmp文件，imp命令可以把dmp文件从本

地导入到远处的数据库服务器中。利用这个功能可以构建两个相同的数据库，一

个用来测试，一个用来正式使用。

执行环境：可以在SQLPLUS.EXE或者DOS（命令行）中执行，

DOS中可以执行时由于在oracle 8i 中安装目录\ora81\BIN被设置为全局路径

，

该目录下有EXP.EXE与IMP.EXE文件被用来执行导入导出。

oracle用java编写，SQLPLUS.EXE、EXP.EXE、IMP.EXE这两个文件有可能是被包装

后的类文件。

SQLPLUS.EXE调用EXP.EXE、IMP.EXE所包裹的类，完成导入导出功能。

下面介绍的是导入导出的实例。

数据导出：

1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp 中

exp system/manager@TEST file=d:\daochu.dmp full=y

2 将数据库中system用户与sys用户的表导出

exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)

3 将数据库中的表inner_notify、notify_staff_relat导出

exp aichannel/aichannel@TESTDB2file= d:\data\newsmgnt.dmp tables=

(inner_notify,notify_staff_relat)

4 将数据库中的表table1中的字段filed1以"00"打头的数据导出

exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\"

where filed1 like '00%'\"

上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。

也可以在上面命令后面加上 compress=y 来实现。

数据的导入

1 将D:\daochu.dmp 中的数据导入 TEST数据库中。

imp system/manager@TEST file=d:\daochu.dmp

imp aichannel/aichannel@HUST full=y file=file= d:\data\newsmgnt.dmp

ignore=y

上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导

入。

在后面加上 ignore=y 就可以了。

2 将d:\daochu.dmp中的表table1 导入

imp system/manager@TEST file=d:\daochu.dmp tables=(table1)

基本上上面的导入导出够用了。不少情况要先是将表彻底删除，然后导入。

注意：

操作者要有足够的权限，权限不够它会提示。

数据库时可以连上的。可以用tnsping TEST 来获得数据库TEST能否连上。

附录一：

给用户增加导入数据权限的操作

第一,启动sql*puls

第二，以system/manager登陆

第三，create user 用户名 IDENTIFIED BY 密码（如果已经创建过用户，这步

可以省略）

第四，GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,

DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,

DBA,CONNECT,RESOURCE,CREATE SESSION TO 用户名字

第五, 运行-cmd-进入dmp文件所在的目录,

imp userid=system/manager full=y file=*.dmp

或者 imp userid=system/manager full=y file=filename.dmp

执行示例:

F:\Work\Oracle_Data\backup>imp userid=test/test full=y

file=inner_notify.dmp

屏幕显示

Import: Release 8.1.7.0.0 - Production on 星期四 2月 16 16:50:05 2006 (c) Copyright 2000 Oracle Corporation. All rights reserved.

连接到: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

With the Partitioning option

JServer Release 8.1.7.0.0 - Production

经由常规路径导出由EXPORT:V08.01.07创建的文件

已经完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的导入

导出服务器使用UTF8 NCHAR 字符集 (可能的ncharset转换)

. 正在将AICHANNEL的对象导入到 AICHANNEL

. . 正在导入表 "INNER_NOTIFY" 4行被导入

准备启用约束条件...

成功终止导入，但出现警告。

附录二：

Oracle 不允许直接改变表的拥有者, 利用Export/Import可以达到这一目的. 先建立import9.par,

然后，使用时命令如下：imp parfile=/filepath/import9.par

例 import9.par 内容如下：

FROMUSER=TGPMS

TOUSER=TGPMS2 （注：把表的拥有者由FROMUSER改为TOUSER，

FROMUSER和TOUSER的用户可以不同）

ROWS=Y

INDEXES=Y

GRANTS=Y

CONSTRAINTS=Y

BUFFER=409600

file==/backup/ctgpc_20030623.dmp

log==/backup/import_20030623.log

Oracle数据库导入导出程序

2008年08月12日星期二 12:43

1.Export工具是一个导出程序

语法：

exp UserId/Password@数据库全局名 file=DMP文件的目录其它参数

获取帮助，敲入：exp help=y

Export常用的参数（1）

Direst 选择是否采用直接路径导出

File指定导出文件名

Full Full=Y将整个数据库导出

Incremental导出上次Incremental 、Cumulative、Complete导出而改变的所

有对象

Cumulative 导出上次Cumulative、Complete导出后改变的所有对象

Complete 导出所有的对象

Indexes 指定索引是否被导出

Export常用的参数（2）

Log 指定日志的文件名

Owner 允许为指定的用户导出对象

Parfile 允许从一个文件读取导出参数

Query 指定从表中导出符合条件的行

Record 指定是否在导出系统表中记录Incremental或Cumulative导出，默认为Y

Rows 控制表数据是否被导出

Tables 允许导入一个或多个表

Triggers 指定触发器是否和表一起导出

2.Import工具是一个导入程序

语法：

imp UserId/Password@数据库全局名 file=DMP文件的目录其它参数

获取帮助，敲入：imp help=y

Import常用的参数（1）

File 指定导入文件名

Fromuser 允许导入指定的用户拥有的表

Full full=Y时，导入DMP文件中所有的事物

Ignore 为Y时，如果数据出错，将继续下一行数据

Indexes 是否导入索引

Log 指定日志文件

Parfile 指定从文件中读导出参数

Rows 指定是否导入表的数据

Import常用的参数（2）

Show 指定显示全部要被执行的参数

Tables 指定导入一个或多个表

Touser 指定导入到哪个用户名下

Userid 指定导入的用户的用户名和密码

3.SQL*Loader简介

SQL*Loader从文本文件导数据到数据库中。功能包括：

从定界文件装数据

从固定长度的文本文件装数据

从二进制文件装数据

在输入文件中过滤数据

SQL*Loader使用的数据类型

Char：该char和数据库中的char类型不同，它包括数据库中的char、varchar、CLOB等类型

Date：时间类型

Interger external：定义整数值

Decimal external：定义包括小数点的数值

装载字段固定长度的文件

一个例子：

Load data

infile ‘yyb_data.txt’

append

Into table emp

( name position(1:10) char,

rank position(11:13) interger external,

birthday position(13:23) date “dd-mm-yyyy”

)

导入有界定符的文件

一个例子：

Load data

Infile ‘c:\yyb.txt’

append

Into table emp

( name char terminated by ‘,’,

rank interger external terminated by ‘,’,

birthday date “dd-mm-yyyy” terminated by ‘,’

)

导入有界定符的文件 (续)

再看一个例子（处理空字段）：

Load data

Infile ‘c:\yyb.txt’

append

Into table emp

trailing nullcols

( name char terminated by ‘,’,

rank interger extern al terminated by ‘,’,

birthday date “dd-mm-yyyy” terminated by ‘,’ )

如何把数据放到表中

从前面的例子中可以看出，它们都包含关键字append，还有其它的关键字：Insert：指定要装载的是空表，否则失败

Append：添加数据到表，即使表中有数据

Replace：加载前，将删除表中的所有数据

Truncate：与replace同

使用SQL*Loader命令和参数

命令语法为：

sqlldr [param=value [,para m= valus … ] ]

参数：

Userid 用户名和密码

Control 指定控制文件名，也就是存放前面的胶片中的代码的文件

Log 指定日志文件名

Bad 指定存放坏记录的文件名

SQL*Loader参数（续前）

Data 指定数据文件名

Skip 允许跳过的记录数量

Load 允许装入的记录数量

Errors 指定允许的错误数

Rows 指定装载多少条记录提交一次

Direct 可以指定为直接路径装载

Parfile 指定包含命令参数的文件名

举个例子来说明:

(1)先创建一张表:

create table test_load(

stu_num number(10,0),

stu_name varchar2(50),

stu_date date

)

(2)准备数据文件(可以采用spool导出,再下来会讲到): dateFile.txt "1234","niegc","2006-01-01"

"1235","ngc","2006-01-02"

(3)写控制文件(必须注意每一个字段的位置,和时间字段的格式):loader.ctl Load data

infile 'F:\test\dateFile.txt'

Append into table test_load

fields Terminated by "," Optionally enclosed by'"'

(

stu_num,

stu_name,

stu_date date "YYYY-MM-DD"

)

(3)执行sqlldr

C:\Documents and Settings\Administrator>sqlldr niegc/niegc

control=f:\test\loader.ctl log=f:\test\aaaaaa.log

SQL*Loader: Release 9.2.0.1.0 - Production on 星期三 4月 26 22:06:35 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

达到提交点，逻辑记录计数2

成功了,是不是很简单,append 还是insert 上面有说明的哦.

4.Spool简介

spool 是数据的导出命令,最简单的执行如下:

用sqlplus 登陆:

SQL>spool f:\test\bbb.txt

SQL> select * from test_load;

STU_NUM

STU_NAME STU_DATE

---------- --------------------------------------------------

----------

1234 niegc 01-1月-06

1235 ngc 02-1月-06

SQL> spool off

这样就可以导出了

如果你想导出自己定义的格式,你可以先set 参数然后再spool

例如:

SET COLSEP ' '

SET ECHO OFF

SET FEEDBACK OFF

SET HEADING OFF

SET PAGESIZE 0

SET LINESIZE 1000

SET NUMWIDTH 12

SET TERMOUT OFF

SET TRIMOUT ON

SET TRIMSPOOL ON