Oracle数据导入导出

1、导入导出思路：

使用Oracle的exp命令将指定数据库导出为dmp文件，然后将dmp文件上传到需要导入的数据库所在服务器，使用Oracle的imp命令将dmp文件导入指定数据库。

2、导出

如： 现有一个10.21.19.63的服务器，其安装有Oracle，有用户 demo，该用户有两个表 user_info， policy_info。现在要将两个表导出。

# 首先进入导出数据存放的目录，要考虑导出数据大小，以及Oracle用户是否对该目录文件有读写功能

# 赋予/home/lijunya/目录所有权限
chmod -R 777 /home/lijunya/

# 进入文件目录下
cd /home/lijunya/

# 切换到Oracle用户
su oracle

# 执行导出命令
exp demo/demo@10.21.19.63/orcl file=demo_user_policy.dmp tables=USER_INFO,POLICY_INFO

导出的日志如下：

[oracle@localhost lijunya]$ exp demo/demo@10.21.19.63/orcl file=demo_user_policy.dmp tables=USER_INFO,POLICY_INFO

Export: Release 11.2.0.1.0 - Production on Tue Oct 29 05:27:25 2019

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 AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                      USER_INFO  150904330 rows exported
. . exporting table                    POLICY_INFO         10 rows exported
Export terminated successfully without warnings.

查看导出后的文件

[oracle@localhost lijunya]$ ll -h
total 7.8G
-rw-r--r-- 1 oracle dba 7.8G Oct 29 05:30 demo_user_policy.dmp

3、将导出的数据文件上传到需要导入的数据库所在服务器上

# 上传命令
[root@localhost lijunya]# scp demo_user_policy.dmp root@10.21.13.14:/home/oracle/script/lijunya
root@10.21.13.14's password: 
demo_user_policy.dmp                                                                                      100% 7891MB  10.2MB/s   12:56    

4、该数据导入服务器上的数据库中（不需要创建表，导出的数据中有创建表的语句）

[oracle@localhost lijunya]$ imp testdb/testdb@10.21.19.63/orcl file=demo_user_policy.dmp tables=USER_INFO,POLICY_INFO

导入日志如下：(耗时可能较长)

[oracle@localhost lijunya]$ imp testdb/testdb@10.21.19.63/orcl file=demo_user_policy.dmp tables=USER_INFO,POLICY_INFO

Import: Release 11.2.0.1.0 - Production on Tue Oct 29 23:09:21 2019

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

Warning: the objects were exported by demo, not by you

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing demo's objects into TESTDB
. importing demo's objects into TESTDB
. . importing table                    "USER_INFO"  150904330 rows imported
. . importing table                  "POLICY_INFO"         10 rows imported
Import terminated successfully without warnings.