oracle数据泵及sqlldr

 

ORALCE 数据泵导入导出数据：

 

导出：

1、从cmd中以dba权限登录数据库：sqlplus system/密码@orcl as sysdba;

2、创建目录：create directory testoutput as 'e:\test';

3、授权：grant read,write on DIRECTORY testoutput to SYS;

4、对应第二步所创建的目录，在数据库服务端的e盘新建文件夹test；

5、退出数据库登录：quit；

6、导出：

Expdp [用户名]/[密码]@[主机字符窜] full=y directory=TEST dumpfile=X.dmp logfile=X.log

执行完以上两步后，即可在e://testoutput文件夹下看到导出的数据文件X.dmp，以及导出日志X.log。

示例命令：

Expdp XREPORT/xreport@orcl11gnet directory=testoutput dumpfile=20200704.dmp logfile=20200704.log FULL=y; 

等待处理完毕后，在e:\testoutput 文件夹中即可查看到文件。

导入：与导出的前面5步相同

1、从cmd中以dba权限登录数据库：sqlplus system/密码@orcl as sysdba;

2、创建目录： Create directory testoutput as 'e:\test';

3、授权：grant read,write on DIRECTORY testoutput to SYS;

4、对应第二步所创建的目录，在数据库服务端的e盘新建文件夹testoutput；

5、退出数据库登录：quit；

6、将待导入的数据文件放在导入数据库服务端的e盘下testoutput文件夹中；

7、导入：

impdp [用户名]/[密码]@[主机字符窜] full=y directory=TEST dumpfile=X.dmp logfile=X.log ignore=y;

示例命令：

impdp system/root@orcl11gnet directory=xreport dumpfile=XREPORT_20200515.dmp logfile = oracleImpdp20161103.log FULL=y ignore=y;

注意：若是第一次导入需要在导入前创建数据空间：

示例命令：

CREATE TABLESPACE XREPORT_DATA DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\XREPORT_DATA01.DBF' SIZE 30G autoextend on maxsize unlimited segment space management auto;

CREATE TABLESPACE XREPORT_INDEX DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\XREPORT_INDEX01.DBF' SIZE 20G;

 

整理后的导入命令如下：

CREATE TABLESPACE XREPORT_DATA DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\XREPORT_DATA01.DBF' SIZE 30G autoextend on maxsize unlimited segment space management auto;

CREATE TABLESPACE XREPORT_INDEX DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\XREPORT_INDEX01.DBF' SIZE 20G;

 

创建Directory文件

select * from dba_data_files;

 

create directory DDUMPDIR as 'E:\dumpdir'

create user XREPORT identified by XREPORT;

grant dba to XREPORT;

 

导入dmp文件，还原数据库文件，打开cmd 输入一下命令：

impdp xreport/xreport directory=DDUMPDIR dumpfile=XREPORT_20200515.DMP

 

下面两个imp的命令可供参考：

imp xreport/xreport file='D:\dumpdir\xpadcData20190630.dmp' tables=XPADC_LCCPXSMX

imp xpadb/xpadb file='E:\ORADATA\XPADC\xpad702.dmp'  full=y

 

 

 

• SQLLDR加载文件

1. Sqlldr在哪

sqlldr加载文件主要依赖sqlldr.exe进行操作，该文件和sqlplus.exe位于同一目录中，我的文件位置为：E:\oracle\product\11.2.0\dbhome_1\BIN\文件夹下

1. Sqlldr是什么，能干什么

Sqlldr为SqlLoader的简写，主要是负责将指定文件中的数据按照指定的策略方式，导入到数据库中。当然，这一步骤通过使用java等 【按行读取文件->根据分隔符split->实例化对象或填充Map->调用持久层框架将数据持久化到数据库中】来实现相同的功能。两者的区别在于，java更轻量级，在小数据量时更轻便灵活，且适配任意数据库。而sqlldr更重量级，配置相较于java略加繁琐，只针对Oracle数据库，但效率却更高。

1. Sqlldr的格式1

Sqlldr致力于导入外部文件中的数据，因此sqlldr要求需要一个后缀名为.ctl的控制文件。该文件中定义一些对文件的读取策略。.ctl文件中必须包含策略格式。

一个简单的.ctl文件如下

 

LOAD DATA

INFILE *

APPEND INTO TABLE DATA

FIELDS TERMINATED BY ","

(COLUMN1,COLUMN2,COLUMN3,COLUMN4)

BEGINDATA

1,1,1,1

2,2,2,2

3,3,3,3

4,4,4,4

 

第二行的星号*表示数据文件在该控制文件中，如果将数据单独分开作为一个文件，这里将换为路径（具体见第三点）

第三行INTO TABLE DATA中的DATA是需要导入数据的表名，最前方是数据插入的方法：

INSERT（缺省时默认取值） 表在非空的时候进行数据插入的操作

APPEND  向表中追加数据

REPLACE   代替->首先清空表中的数据，重新进行写入

第四行定义每个字段之间的分割方式。

第五行提供了对齐的列名。

第六行是数据开始的标志。

接下来每一行都是按行待处理的数据。

 

1. Ctl的格式2（独立数据文件与控制文件）

LOAD DATA

INFILE 'C:\Users\dell\Desktop\Oracle\Project\2019.03.18\loadData.dat'

APPEND INTO TABLE DATA

FIELDS TERMINATED BY ","

(COLUMN1,COLUMN2,COLUMN3,COLUMN4)

 

相比与第一种，在第二行中绝对定位到数据文件的位置。其他地方和第一种相似。

 

1. 使用sqlldr

格式：SQLLDR 用户名/密码 CONTROL=文件路径，

其中，这里的文件路径不用加引号。

示例：sqlldr userid=XREPORT/xreport@orcl11gnet control=E:\ctlcfg\CUSM.ctl

使用sqlldr导入数据时，也会产生执行日志和错误日志等， 可手动定位日志路径

log=E:\ctlcfg\0191460D.i2220251215.log bad=E:\ctlcfg\0191460D.i2220251215.bad。

Log定义success日志，bad存放错误日志。

组合在一起后格式为：sqlldr userid=XREPORT/xreport@orcl11gnet control=E:\ctlcfg\CUSM.ctl log=E:\ctlcfg\0191460D.i2220251215.log bad=E:\ctlcfg\0191460D.i2220251215.bad。