Oracle数据库的数据导入导出（PL/SQL软件操作导入导出、SQL LOADER的应用、数据泵导入导出）

PL/SQL软件操作导入导出

序

类型

属性

说明

1

导出

cmd命令导出： exp 用户名/密码@orcl file=指定文件的绝对路径 tables=导出表名字

第一种导出方式

2

Tools→Export Tables →Oracle Export→Export Import中导进product11.2.0dbhome_1BINexp.exe运行程序→Output file中填写导出文件后所在位置 →Export （注：选中导出的表）

第二种导出方式

3

exp scott/tiger@orcl file=F:/xb/scott.txt owner=scott –导出整个用户的表

4

导入

cmd命令导入：imp 新用户名/密码@orcl file=绝对路径 fromuser=文件的原有用户名  touser=新用户名

第一种导入方式

5

Tools→Import Tables →From User中填写导入文件的用户名→To User 中填写被导入文件的用户名 →Import Import中导进product11.2.0dbhome_1BINexp.exe运行程序 →Output file中填写需要导入的文件

第二种导入方式

SQL LOADER的应用

序

语句

说明

1

创建文件夹bad(坏文件)、data(数据文件.txt)、log(日志文件)、ctl(控制文件)、bat(执行控制文件)

2

set 属性；
spool  路径数据文件名（保存成txt的文本） 查询语句;  –导出数据语句
spool off              –收集完毕
exit；

1.在UE中写入收集数据的语句，保存成txt文本，路径为数据文件的路径。
2.也可以直接cvs把导出的数据，存储到数据文件中。

3

@spool的绝对路径；

4

options(rows=128)
load data infile “数据文件的绝对路径”
truncate  –操作类型，用 truncate table 来清除表中原有记录
into table 表名
fields terminated by’,’   –每行记录分割以数据文件中为准
optionally enclosed by ‘”‘  –每个字段分割以数据文件中为准
trailing nullcols
(virtual_column FILLER,  –虚拟字段,可忽略
表中全部字段)

1.用UE做控制文件 保存成ctl文件；
2.注：”trim(:字段)” –去空

5

sqlldr 用户名/用户密码@orcl control=控制文件的绝对路径 log=日志的绝对路径 bad=坏文件的绝对路径；

1.执行控制文件文件 保存成bat批处理文件
2. 日志，坏文件不需要创建，直接路径文件名.扩展名，在cmd中创建输写。

第二步：txt文本的属性

序

属性

说明

1

set line 1000

设置行的长度

2

set pagesize 0

输出不换页

3

set feedback off

默认的当一条sql发出的时候，oracle会给一个反馈，比如说创建表的时候，如果成功命令行会返回类似：Table created的反馈,off后不显示反馈

4

set heading off

不显示表头信息

5

set trim spool on

如果trimspool设置为on，将移除spool文件中的尾部空

6

set trims on

去掉空字符

7

set echo off

显示start启动的脚本中的每个sql命令，缺省为on

8

set colsep ‘|’

设置分隔符

9

set termout off

不在屏幕上显示结果

10

注：导出：trimspool on 和 trims on 属性不能在cmwd中使用，应在ue中编写，可以在comwd中@spool的绝对路径；

数据泵导入导出

序

类型

语句

说明

1

导出

在磁盘中创建一个文件夹（目录）用于存放导出的数据文件

2

sqlplus system(sys)/密码@orcl as sysdba;(直接在oracle中在sys用户下操作也可以)

1.在cmd运行窗口执行此语句；
2.或直接在PL/SQL中的sys用户进行以下操作也可以

3

create or replace directory 目录名 as ‘目录绝对路径’；

指定数据导出的目录

4

select * from dba_directories ;

查看数据库中存放的指定路径

5

grant read,write on directory 目录名 to 用户名;

给指定用户赋目录权限

6

quit

cmd退出数据库登录,在cmd模式下执行以上操作,使用此条

7

expdp 用户名/密码@orcl  directory=目录名  dumpfile=导出的文件名.dmp  logfile=日志名.log schemas=用户名；

切换至cmd运行窗口直接执行命令

8

导入

在磁盘中创建一个目录，将要导入的数据文件放在此目录中

9

sqlplus system(sys)/密码@orcl as sysdba;(直接在oracle中在sys用户下操作也可以)

1.在cmd运行窗口执行此语句；
2.或直接在PL/SQL中的sys用户进行以下操作也可以

10

create directory 目录名 as ‘目录绝对路径’

指定数据文件的目录所在位置，与第一条创建的目录保持一致。如果指定，则无需执行此语句

11

create user 用户名 identified by 用户密码

创建用户，也可以不用先创建用户，直接执行导入语句

12

grant connect,resource,dba to 用户名

给用户授权

13

grant read,write on directory 目录名 to 用户名;

把目录权限给要导入数据的用户

14

quit

cmd退出数据库登录,在cmd模式下执行以上操作,使用此条

15

impdp [用户名]/[密码]@orcl（数据库实例名） directory=目录名 dumpfile=数据文件.dmp logfile=日志名.log remap_schema=导出数据源的数据库名:导入数据库名

cmd中直接导入

16

select * from dba_directories

查看所有指定数据文件的目录路径