Oracle数据库的数据备份,本地,异地,exp-imp,expdp-impdp等实例讲解
测试环境:操作方面,还是熟悉的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就分开另外一篇写吧,不凑到一起了。等写完了再补链接。
上一篇: 甜咖啡有哪些呢?什么时候喝咖啡最合适呢?