最近用到 Oracle 导出导入数据,在网上看了别人的一些文章,总结如下:
Oracle 导出导出有两中方式:一、利用 exp imp 导出导入;二、利用 Oracel 数据泵 expdp impdp 导出导入。
一、利用 exp imp 导出导入
exp imp 语法如下:
exp:
1) 将数据库 orcl 完全导出
exp system/manager@orcl file=d:\orcl_bak.dmp full=y
2) 将数据库中 system 用户的表导出
exp system/manager@orcl file=d:\system_bak.dmp owner=system
3) 将数据库中表 table1,table2 导出
exp system/manager@orcl file=d:\table_bak.dmp tables=(table1,table2)
4) 将数据库中的表 customer 中的字段 mobile 以 "139" 开头的数据导出
exp system/manager@orcl file=d:\mobile_bak.dmp tables=customer query=\"where mobile like'139%'\"
imp:
1) 将备份文件 bak.dmp 导出数据库
imp system/manager@orcl file=d:\bak.dmp
如果数据表中表已经存在,会提示错误,在后面加上 ignore=y 就可以了。
2) 将备份文件 bak.dmp 中的表 table1 导入
imp system/manager@orcl file=d:\bak.dmp tables=(table1)
exp imp 导出导入数据方式的好处是只要你本地安装了 Oracle 客户端,你就可以将服务器中的数据导出到你本地计算机。同样也可以将 dmp 文件从你本地导入到服务器数据库中。但是这种方式在 Oracle11g 版本中会出现一个问题:不能导出空表。Oracle11g 新增了一个参数 deferred_segment_creation,含义是段延迟创建,默认是 true。当你新建了一张表,并且没用向其中插入数据时,这个表不会立即分配 segment。
解决办法:
1、设置 deferred_segment_creation 参数为 false 后,无论是空表,还是非空表,都分配 segment。
在 sqlplus 中,执行如下命令:
SQL>alter system set deferred_segment_creation=false;
查看:
SQL>show parameter deferred_segment_creation;
该值设置后,只对后面新增的表起作用,对之前建立的空表不起作用,并且注意要重启数据库让参数生效。
2、使用 ALLOCATE EXTEN
使用 ALLOCATE EXTEN 可以为数据库对象分配 Extent,语法如下:
alter table table_name allocate extent
构建对空表分配空间的 SQL 命令:
SQL>select 'alter table'||table_name||'allocate extent;' from user_tables where num_rows=0
批量生成要修改的语句。
然后执行这些修改语句,对所有空表分配空间。
此时用 exp 命令,可将包括空表在内的所有表导出。
二、利用 expdp impdp 导出导入
在 Oracle10g 中 exp imp 被重新设计为 Oracle Data Pump(保留了原有的 exp imp 工具)
数据泵与传统导出导入的区别;
1) exp 和 imp 是客户端工具,他们既可以在客户端使用,也可以在服务端使用。
2) expdp 和 impdp 是服务端工具,只能在 Oracle 服务端使用。
3) imp 只适用于 exp 导出文件,impdp 只适用于 expdp 导出文件。
expdp 导出数据:
1、为输出路径建立一个数据库的 directory 对象。
create or replace directory dumpdir as 'd:\';
可以通过:select * from dba_directories; 查看。
2、给将要进行数据导出的用户授权访问。
grant read,write on directory dumpdir to test_expdp;
3、将数据导出
expdp test_expdp/test_expdp directory=dumpdir dumpfile=test_expdp_bak.dmp logfile=test_expdp_bak.log schemas=test_expdp
注意:这句话在 cmd 窗口中运行,并且最后不要加分号,否则会提示错误。因为这句话是操作系统命令而不是 SQL。
impdp 导入数据:
1、给将要进行数据导入的用户授权访问。
grant read,write on directory dumpdir to test_impdp;
2、将数据导入
impdp test_impdp/impdp directory=dumpdir dumpfile=test_expdp_bak.dmp remap_schema=test_expdp:test_impdp
最后:这些内容是我在查找资料时找到,由于比较杂乱,现在稍作整理,方便以后查阅。同时,如果能给需要的人提供一些帮助,那就再好不过了。
来源: https://juejin.im/entry/5a33284b51882554b83795de