导入导出】Oracle 常用导入导出工具集锦-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

导入导出】Oracle 常用导入导出工具集锦

简介:

《Oracle 常用导入导出工具集锦》

新年新群招募: 中国Oracle精英联盟 170513055群介绍:本群是大家的一个技术分享社区,在这里可以领略大师级的技术讲座,还有机会参加Oracle举办的技术沙龙,与兴趣相投的小伙伴一起笑谈风云起,感悟职场情!

 数据库版本

SYS@LEO1>select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

操作系统信息

[oracle@leonarding1 admin]$ uname -a

Linux leonarding1.oracle.com 2.6.32-200.13.1.el5uek #1 SMP Wed Jul 27 21:02:33 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

 比较使用sql*loader的直接加载方式和传统加载方式的性能差异,给出演示过程和结论。

第一 我们先要生成平面数据(文本数据)

LEO1@LEO1>create table leo2 as select * from dba_objects;      创建数据源,我们的平面数据就是从这个表中取出

Table created.

第二 我们利用spool工具将屏幕中显示出来的记录写入到指定文件,这样我们就可以得到一个平面文件啦

set termout off;            是否在屏幕上显示输出内容,off屏幕不显示查询语句,主要与spool结合使用

set feedback off;           关闭本次sql命令处理的记录条数,默认为on即去掉最后的已经选择的行数

set echo off;              关闭脚本中正在执行的SQL语句的显示

set heading off;            关闭标题的输出,设置为off就去掉了select结果的字段名只显示数据

set trimout on;            去除标准输出每行后面多余的空格

set trimspool on;          将每行后面多余的空格去掉【linesize-实际字符数=多余空格】

 

spool /home/oracle/sql_loader/leo3.txt      在屏幕上的所有内容都包含在该文件中

select owner||','||object_name||','||object_id||','||object_type from leo2;   

spool off                         只有关闭spool输出,才会在输出文件中看到输出的内容

备注:在实用SPOOL输出内容到本地文件时,需注意编码格式,否则会出现乱码的问题

[oracle@leonarding1 sql_loader]$ ll

total 28468

-rw-r--r-- 1 oracle oinstall  3246601 Jun 22 14:06 leo3.txt          已经生成平面文件leo3.txt

[oracle@leonarding1 sql_loader]$ cat leo3.txt | wc -l              文件中有72678行记录

72678

第三 创建装入的表leo3_loader

LEO1@LEO1>create table leo3_loader

 (

   owner       varchar2(30),

   object_name varchar2(130),

   object_id   number,

   object_type varchar2(20)

 );

  2    3    4    5    6    7 

第四 创建sql*loader的控制文件leo3_loader.ctl

[oracle@leonarding1 sql_loader]$ vim leo3_loader.ctl

load data

infile '/home/oracle/sql_loader/leo3.txt'                             待加载的数据文件

badfile '/home/oracle/sql_loader/leo3_bad.txt'                       格式不匹配写入坏文件

discardfile '/home/oracle/sql_loader/leo3_discard.txt'                  条件不匹配写入丢弃文件

append into table leo3_loader                                     追加的方式插入数据

fields terminated by ","                                           字段与字段之间的分隔符

trailing nullcols                                                 这句的意思是将没有对应值的列都置为null

(owner,object_name,object_id,object_type)                         数据插入的对应字段

 

第五 执行sqlldr直接加载命令

[oracle@leonarding1 sql_loader]$ sqlldr leo1/leo1 control=leo3_loader.ctl log=leo3_loader.log direct=true

SQL*Loader: Release 11.2.0.1.0 - Production on Sat Jun 22 14:08:31 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Load completed - logical record count 72678.

已经加载了72678行,条件不匹配有72行,实际加载入72606行

LEO1@LEO1>select count(*) from leo3_loader;   表中也是

     72606

我们在看一下sql*loader日志

。。。省略前部份。。。

Total logical records skipped:          0

Total logical records read:         72678

Total logical records rejected:         0

Total logical records discarded:       72                   条件不匹配有72行

Total stream buffers loaded by SQL*Loader main thread:       17

Total stream buffers loaded by SQL*Loader load thread:        6

 

Run began on Sat Jun 22 14:08:31 2013

Run ended on Sat Jun 22 14:08:34 2013

 

Elapsed time was:     00:00:02.60                       所用耗时2.6秒

CPU time was:         00:00:00.13

使用conventional传统加载方式写入数据

LEO1@LEO1>truncate table leo3_loader;                   清空表在加载一次

[oracle@leonarding1 sql_loader]$ sqlldr leo1/leo1 control=leo3_loader.ctl log=leo3_loader.log

LEO1@LEO1>select count(*) from leo3_loader;   表中也是

     72606

我们在看一下sql*loader日志

。。。省略前部份。。。

Total logical records skipped:          0

Total logical records read:         72678

Total logical records rejected:         0

Total logical records discarded:       72                   条件不匹配有72行

 

Run began on Sat Jun 22 15:25:45 2013

Run ended on Sat Jun 22 15:26:05 2013

 

Elapsed time was:     00:00:20.79                       所用耗时2.6秒

CPU time was:         00:00:00.48

小结:经过比对direct比conventional要提高了20倍效率,为什么direct会这么高效呢,下面我们来说说这两种的区别。

Direct 特点

(1)数据绕过SGA直接写入磁盘的数据文件

(2)数据直接写入高水位线HWM之后的新块,不会扫描HWM之前的空闲块

(3)commit之后移动HWM他人才能看到

(4)不对已用空间进行扫描

(5)使用direct几乎不产生redo log,不是完全不产生(安全性差),但会产生undo数据

(6)适用OLAP在线分析场景,增 删 改不频繁的场景

Conventional传统加载特点

(1)数据先加载 -> SGA -> 磁盘的数据文件

(2)会扫描高水位线HWM之前的数据块,如果有空闲块(碎片经常DML导致)就会利用,如果没有再插入新块

(3)高水位线HWM之前的数据块是放在SGA区的

(4)会产生redo log和undo数据

(5)安全性高,可恢复数据

(6)传统加载与SQL语句insert插入没区别

 

 比较数据泵和exp/imp对相同数据导出/导入的性能差异,给出演示过程

EXP/IMP 是数据库传统的导出导入工具,它有使用方便,在服务端和客户端都可用的优点,速度没有expdp快,功能没有expdp强大

EXP  工具帮助文档,我们可以参考帮助文档进行命令行配置

[oracle@leonarding1 ~]$ exp –help    或者    exp help=y

 

Export: Release 11.2.0.1.0 - Production on Thu Jun 20 07:28:33 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

 

 

You can let Export prompt you for parameters by entering the EXP

command followed by your username/password:

 

     Example: EXP SCOTT/TIGER

 

Or, you can control how Export runs by entering the EXP command followed

by various arguments. To specify parameters, you use keywords:

 

     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

 

USERID must be the first parameter on the command line.

 

Keyword    Description (Default)      Keyword      Description (Default)

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

USERID     username/password          FULL         export entire file (N)

BUFFER     size of data buffer        OWNER        list of owner usernames

FILE       output files (EXPDAT.DMP)  TABLES       list of table names

COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record

GRANTS     export grants (Y)          INCTYPE      incremental export type

INDEXES    export indexes (Y)         RECORD       track incr. export (Y)

DIRECT     direct path (N)            TRIGGERS     export triggers (Y)

LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)

ROWS       export data rows (Y)       PARFILE      parameter filename

CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)

 

OBJECT_CONSISTENT    transaction set to read only during object export (N)

FEEDBACK             display progress every x rows (0)

FILESIZE             maximum size of each dump file

FLASHBACK_SCN        SCN used to set session snapshot back to

FLASHBACK_TIME       time used to get the SCN closest to the specified time

QUERY                select clause used to export a subset of a table

RESUMABLE            suspend when a space related error is encountered(N)

RESUMABLE_NAME       text string used to identify resumable statement

RESUMABLE_TIMEOUT    wait time for RESUMABLE

TTS_FULL_CHECK       perform full or partial dependency check for TTS

VOLSIZE              number of bytes to write to each tape volume

TABLESPACES          list of tablespaces to export

TRANSPORT_TABLESPACE export transportable tablespace metadata (N)

TEMPLATE             template name which invokes iAS mode export

 

Export terminated successfully without warnings.

 

创建测试表leo1

LEO1@LEO1>set linesize 400 pagesize 999                   格式化

LEO1@LEO1>drop table leo1 purge;                        清空环境

Table dropped.

LEO1@LEO1>create table leo1 as select * from dba_objects;     创建测试表leo1

Table created.

LEO1@LEO1>insert into leo1 select * from leo1;

72543 rows created.

LEO1@LEO1>insert into leo1 select * from leo1;

145086 rows created.

LEO1@LEO1>insert into leo1 select * from leo1;

290172 rows created.

LEO1@LEO1>insert into leo1 select * from leo1;

580344 rows created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>insert into leo1 select * from leo1;              

1160688 rows created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select count(*) from leo1;                      现在是232万

  COUNT(*)

----------

   2321376

[oracle@leonarding1 ~]$ mkdir exp_dump                    创建一个导出文件的文件夹

[oracle@leonarding1 ~]$ exp leo1/leo1 file='/home/oracle/exp_dump/leo1.dmp' tables=leo1 rows=y

Export: Release 11.2.0.1.0 - Production on Fri Jun 21 06:38:28 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses ZHS16GBK character set (possible charset conversion)

 

About to export specified tables via Conventional Path ...

. . exporting table                           LEO1    2321376 rows exported

Export terminated successfully without warnings.  成功终止导出,没有出现告警

[oracle@leonarding1 ~]$ date

Fri Jun 21 06:39:40 CST 2013

导出从06:38:28开始到06:39:40结束,一共用时72秒

LEO1@LEO1>truncate table leo1;                           清空表,我再导入一下

Table truncated.

[oracle@leonarding1 ~]$ cd exp_dump/

[oracle@leonarding1 exp_dump]$ ll

total 261356

-rw-r--r-- 1 oracle oinstall 267362304 Jun 21 06:39 leo1.dmp      这个就是我们导出的文件

[oracle@leonarding1 exp_dump]$ imp leo1/leo1 file='/home/oracle/exp_dump/leo1.dmp' full=y ignore=y

 

Import: Release 11.2.0.1.0 - Production on Fri Jun 21 06:45:04 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

Export file created by EXPORT:V11.02.00 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses ZHS16GBK character set (possible charset conversion)

. importing LEO1's objects into LEO1

. importing LEO1's objects into LEO1

. . importing table                         "LEO1"    2321376 rows imported

Import terminated successfully without warnings.  成功终止导入,没有出现告警

[oracle@leonarding1 exp_dump]$ date

Fri Jun 21 06:48:26 CST 2013

导入从06:45:04开始到06:48:26结束,一共用时202秒

 

EXPDP/IMPDP 是Oracle推崇的数据泵导入导出工具,用于代替传统的EXP/IMP,只能在服务端使用,效率比EXP/IMP快几十倍,有续传功能和并行功能。这个工具始于Oracle 10g,从Oracle 11g开始不再提供老的EXP/IMP的咨询但工具还可以使用。

EXPDP工具的效率差不多比EXP快几倍

IMPDP工具的效率差不多比IMP快几十倍

所以说这两个工具适用于大数据导入导出的场景

导出文件的格式更接近于数据库本身的文件格式,避免了数据写入文件时的转换

直接路径加载,跳过SGA内存区,直接加载到高水位线之后

元数据metadata和数据data在导出的过程中可以重叠进行,提高导出的效率。

 

我们再来看看EXPDP/IMPDP的导出导入效果

在使用EXPDP/IMPDP之前我们需要定义一个目录对象,告知EXPDP/IMPDP工具导出的文件和导入的文件的存放目录

LEO1@LEO1>create directory exp_dump as '/home/oracle/exp_dump';

Directory created.

[oracle@leonarding1 exp_dump]$ expdp leo1/leo1 directory=exp_dump dumpfile=expdp_leo1.dmp tables=leo1;

 

Export: Release 11.2.0.1.0 - Production on Fri Jun 21 07:07:09 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "LEO1"."SYS_EXPORT_TABLE_01":  leo1/******** directory=exp_dump dumpfile=expdp_leo1.dmp tables=leo1

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 264 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "LEO1"."LEO1"                               223.9 MB 2321376 rows

Master table "LEO1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for LEO1.SYS_EXPORT_TABLE_01 is:

  /home/oracle/exp_dump/expdp_leo1.dmp

Job "LEO1"."SYS_EXPORT_TABLE_01" successfully completed at 07:08:22

导出从07:07:09开始到07:08:22结束,一共用时73秒,导出文件大小223.9MB 行数2321376 rows,与exp时间相差1秒,expdp在数据量比较大的时候会体现出高效率。

现在我们impdp导入

LEO1@LEO1>drop table leo1;   先把表删除,因为如果表结构存在的话,impdp会认为元数据已存在报错,不可导入

Table dropped.

[oracle@leonarding1 exp_dump]$ impdp leo1/leo1 directory=exp_dump dumpfile=expdp_leo1.dmp

 

Import: Release 11.2.0.1.0 - Production on Fri Jun 21 07:24:07 2013

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "LEO1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "LEO1"."SYS_IMPORT_FULL_01":  leo1/******** directory=exp_dump dumpfile=expdp_leo1.dmp

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "LEO1"."LEO1"                               223.9 MB 2321376 rows

Job "LEO1"."SYS_IMPORT_FULL_01" successfully completed at 07:27:27

导入从07:24:07开始到07:27:27结束,一共用时200秒,导入文件大小223.9MB 行数2321376 rows,比imp时间节约了2秒,impdp也是在数据量比较大的时候会体现出高效率。

 

 

 用外部表的方式查询当天数据库alert日志文件中当天所有的ora-错误信息,给出演示过程。

外部表:表中的数据以操作系统文件的方式来存放,现在表中的数据不是放在数据库中了而是放在操作系统上面,Oracle提供了一种直接读取外部数据的机制。

外部表好处:1.数据二次开发

            2.大数据量迁移

            3.充分利用操作系统空间

            4.不占用数据库空间

            5.支持标准SQL条件检索

外部表也需要目录对象的支持,通过目录对象可以知道从哪个目录读取文本数据

LEO1@LEO1>create directory alert as '/u02/app/oracle/diag/rdbms/leo1/LEO1/trace';

Directory created.

这是Oracle 11g 告警日志目录

grant read,write on directory alert to public;            对这个目录对象授予读/写权限,并授予所有用户

LEO1@LEO1>select * from dba_directories;

OWNER         DIRECTORY_NAME                 DIRECTORY_PATH

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

SYS             EXP_DUMP                      /home/oracle/exp_dump

SYS             XMLDIR                         /u02/app/oracle/product/11.2.0/db_1/rdbms/xml

SYS             ALERT                          /u02/app/oracle/diag/rdbms/leo1/LEO1/trace

SYS             DATA_PUMP_DIR                 /u02/app/oracle/admin/LEO1/dpdump/

SYS             ORACLE_OCM_CONFIG_DIR        /u02/app/oracle/product/11.2.0/db_1/ccr/state

我们下面就是Oracle告警日志文件当作数据库的一个外部数据源来访问,我们使用外部表的方式抽取alert日志数据,然后使用标准SQL语句来检索“ora-错误信息”。

下面我们就来创建一个外部表

LEO1@LEO1>create table leo_alert (content varchar2(4000))     alert日志数据量多因此字符串设置的大一点

organization external

(

type oracle_loader                                       如果你设置的是oracle_datapump请修改为loader

default directory alert

access parameters (

records delimited by newline                               每条记录用换行区分

nobadfile                                               没有坏文件,丢弃文件,日志文件

nodiscardfile

nologfile

)

location ('alert_LEO1.log')                                  加载告警日志文件内容

);  2    3    4    5    6    7    8    9   10   11   12   13 

LEO1@LEO1>select count(*) from leo_alert;                   一共7198条

  COUNT(*)

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

      7198

我们抽取其中10条ORA-开头的错误记录显示出来

LEO1@LEO1>select * from leo_alert where content like '%ORA-%' and rownum<=10;

CONTENT

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

ORA-210 signalled during: create tablespace test datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off...

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u02/app/oracle/oradata/LEO1/control01.ctl'

ORA-27041: unable to open file

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u02/app/oracle/oradata/LEO1/control01.ctl'

ORA-27041: unable to open file

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u02/app/oracle/oradata/LEO1/control01.ctl'

ORA-27037: unable to obtain file status

10 rows selected.

小结:这里需要注意几个问题,我们在创建外部表的时候需要设置没有坏文件,丢弃文件,日志文件参数否则会报错ORA-29913: error in executing ODCIEXTTABLEOPEN callout。

 

sql*loader exp/imp expdp/impdp organization_external direct

 

刘盛Leonarding
2013.6.22
北京&summer
分享技术~成就梦想
Blog:www.leonarding.com






 本文转自 ztfriend 51CTO博客,原文链接:http://blog.51cto.com/leonarding/1625388,如需转载请自行联系原作者


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章