欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle数据库的数据备份,本地,异地,exp-imp,expdp-impdp等实例讲解

程序员文章站 2022-07-01 19:34:22
测试环境:操作方面,还是熟悉的windows 10,是oracle database 11g r2。 一、exp--imp (一)exp导出 exp导出可一次导出整个数据库、或者按用户导出、或者导出...

测试环境:操作方面,还是熟悉的windows 10,是oracle database 11g r2。

一、exp--imp

(一)exp导出

exp导出可一次导出整个数据库、或者按用户导出、或者导出指定表、或者指定表的部分数据(通过select子句)

还有个要说明一下,就是exp命令后面跟着的“用户/密码”,如果你这个用户有dba权限,那你就可以导出整个数据库能导出的数据,像导全库,跨用户导数据,都需要登录的这个用户有dba权限,否则的话,就只能导自己用户下的数据。

所以呢,下面的代码我全部都用system这个用户来登录验证,system的密码我简单设置为oracle。导出导入的代码中习惯性加入输出日志的参数,log=xxx.log,方便导完后查看记录。

1、导整个库

导出文件orclall.dmp,输入日志orclall.log,full=y即为导出全库,代码如下。

exp system/oracle@orcl file=orclall.dmp log=orclall.log full=y

2、导用户

exp可以按用户来进行数据导出,可一次导单个或者多个用户,通过owner这个参数来设置导出的用户,例如orcl数据库下的lhy用户和test用户。

exp system/oracle@orcl file=owner.dmp log=owner.log owner=(lhy,test)     --导两个用户
exp system/oracle@orcl file=lhy.dmp log=lhy.log owner=lhy     --导lhy用户

3、导表

导表与导用户类似,例如我们要导出用户名为lhy下的empdata表和orgdata表。

下面两句语法,不同的是一个登陆的是system用户,一个是lhy用户,登录system用户导lhy用户的表的时候需要把表名写成“用户名.表名”(其实那个应该叫模式吧好像,不过习惯说是用户下的某某表),大概知道是这个意思就行。

然后第二句的话直接登录lhy用户,所以导当前登录用户的表就不需要在表名前面加上用户名了。

exp system/oracle@orcl file=exptables.dmp log=exptables.log tables=(lhy.orgdata,lhy.empdata)
exp lhy/lhy@orcl file=exptables.dmp log=exptables.log tables=(orgdata,empdata)

另外,如果是单个表特别大,不希望每次都导出所有数据的,我们可以通过查询的方式控制导出的数据集,例如我只希望导出empdata表yearno字段的值为2017的数据,在查询中,我们可以写

select * from empdata where yearno = '2017';

然后,我们这个条件导出的语法就是

exp lhy/lhy@orcl file=empdata2017.dmp log=empdata2017.log tables=(empdata) query=\" where yearno = '2017'\"

4、exp常用参数

(1)导出数据行(rows)

默认为rows=y,如果只需要导出表结构而不需要导出表数据,可以设置rows=n。

(2)数据缓冲区(buffer)

buffer大概可以理解为控制导出时单次处理的最大数据量。在导出大批量数据的时候,相对调高buffer值会有效提高导出的速度。

(3)转储文件最大大小(filesize)

指定导出的单个dmp文件最大容量。如filesize=1024m。

(4)直接路径 (direct)和io记录的长度(recordlength)

常规导出是走的常规路径,通过select语句查到表数据,通过buffer缓冲区等等的处理后再写入文件,而直接路径的会跳过sql语句处理这部分,不走buffer缓冲区,正常情况下速度会比通过常规路径导出快很多。

在导出语法中加入direct=y即通过直接路径导出,另外设置recordlength参数也会对直接路径导出的速度有所影响,recordlength最大可设置为65535,一般都可以直接设置recordlength=65535。

direct导出也有一定的限制,例如不支持表空间传输和上面的query查询导出。而且本地环境变量的nls_lang参数要跟数据库的字符集一致。

(二)imp导入

导入之前一般都需要保证存在表空间和用户,例如全库导入或者按用户导入,首先得在目标数据库存在这个用户,否则在导入数据的时候就会报这个用户不存在的错误。

1、全库导入

全库导入的跟导出的基本一样,就换了下命令

imp system/oracle@orcl file=orclall.dmp log=orclall.log full=y

2、按用户导入

按用户导入的会比导出时需要多两个参数,fromuser和touser,顾名思义,就是那个很有趣的哲学命题,我从哪里来?我要到哪里去?

imp system/oracle@orcl file=owner.dmp log=owner.log fromuser=lhy touser=lhy   --从原来的lhy用户导入到目标数据库的lhy用户
imp system/oracle@orcl file=owner.dmp log=owner.log fromuser=lhy touser=test  --从原来的lhy用户导入到目标数据库的test用户

3、按表

跟导出的基本一致,可导单表或者多表。

imp lhy/lhy@orcl file=exptables.dmp log=exptables.log tables=(orgdata,empdata)  --可导入单表或者多表

4、imp的一些常用参数

(1)忽略创建错误(ignore)

因为imp导入表数据的步骤是create table -> insert into -> create index等等,在导入的时候,如果某个表已经存在数据库中。导入在create table这步就会报错,然后默认跳过这个对象。

如果我们需要进行数据导入,可添加参数ignore=y,这样的话就会忽略create的错误,把dmp中该表的数据insert 到目标数据库目标表中,如正常insert一样,遇到主键或约束之类的问题依然会报错,但不影响不冲突的那部分数据的导入。

(2)导入数据行(rows)

很多时候,我们往往只需要数据中的表结构部分,而不需要表数据,这种情况可通过设置参数rows=n不导入数据,只导入结构。

(3)数据缓冲区(buffer)和提交(commit)

commit的默认设定是n,但如果是大表的导入,commit=n的时候会在最后统一提交,耗用大量undo表空间,这种情况下建议设置commit=y,导入时对数据进行分批提交。

但频繁的提交肯定会对性能有一定的影响,这时候我们需要合理设置一下数据缓冲区buffer的大小,因为commit是根据buffer的大小进行分批提交。

匆匆忙忙再写下,exp-imp导异地数据库的不需要特殊处理,只需要配个本地服务名,能访问到数据库就可以进行导出到本地或者导入到数据库。expdp和impdp的话就麻烦一些,后面再讲。

发现简单写了下exp-imp就已经写了那么多,expdp和impdp就分开另外一篇写吧,不凑到一起了。等写完了再补链接。