Oracle数据库导入与导出方法简述

说明：
1、数据库数据导入导出方法有多种，可以通过exp/imp命令导入导出，也可以用第三方工具导出，如：PLSQL
2、如果熟悉命令，建议用exp/imp命令导入导出，避免第三方工具版本差异引起的问题，同时效率更高，但特别注意：采用命令时要注意所使用的用户及其权限等细节。
3、在目标数据库导入时需要创建与导出时相同的用户名（尽量一致），并赋予不低于导出时用户的权限；同时还需创建与原数据库相同的表空间名，若本地数据库已存在相同的表空间，则只能进行表空间扩充。

一、导入前准备工作（在目标数据库操作）

知识补充：
表空间
Oracle数据库是通过表空间来存储物理表的，一个数据库实例可以有N个表空间，一个表空间下可以有N张表。
表空间(tablespace)是数据库的逻辑划分，每个数据库至少有一个表空间（称作SYSTEM表空间）。为了便于管理和提高运行效率，可以使用一些附加表空间来划分用户和应用程序。
例如：USER表空间供一般用户使用，RBS表空间供回滚段使用。一个表空间只能属于一个数据库。

1、登录服务器
用Xshell、secureCRT、MobaXterm工具都可
2、查询磁盘空间是否足够大
执行df -h或者df -H命令进行查询，若可用空间不足则更换新的目标环境再继续其他操作。

 [oracle@orac ~]$ df -h

3、查询表空间详情
（1）使用终端登录，依次执行命令：

[oracle@orac ~]$ su - oracle （切换到oracle用户（linux的一个用户名））

在/home/oracle/app/oradata 目录下新建一个文件夹，后面创建表空间需要用到，该路径不唯一，根据目标数据库存放数据文件位置而定。

[oracle@orac ~]$ /home/oracle/app/oradata

[oracle@orac ~]$ mkdir snail

（2）登录数据库

[oracle@orac ~]$ sqlplus / as sysdba

执行sql语句

select a.tablespace_name,a.bytes/1024/1024 "sum MB", (a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB", round (((a.bytes-b.bytes)/a.bytes)*100,2) "used%"

from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,

(select tablespace_name,sum(bytes) bytes,max (bytes) largest from dba_free_space group by tablespace_name)b

where a.tablespace_name=b.tablespace_name order by ((a.bytes-b.bytes)/a.bytes) desc;

即可得到当前数据库的表空间情况，如下图：

注：如果要导入的数据库表空间名与当前已有表空间名相同，则无需新建表空间（也不可以再建），但是必须确定已有表空间大小充足，或者已经设置为自动增加并且自动增加的最大值足够大，那么就不需要进行表空间扩充，直接使用该表空间级即可，跳过第四步。反之如果该名称的表空间，或者表空间大小不足以存放要导入的数据时，需要对该表空间进行扩充，执行第四步。

4、表空间扩充
对表空间进行扩充有多种方法，简单介绍其中几个常用方法：
（1）直接增大表空间的大小：

先查看表空间中数据文件存放的位置

SQL> select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;

确定数据文件位置后，执行命令：

  alter database datafile ‘数据文件路径’ resize 要增大的大小

例如：

SQL> alter database datafile '/home/oracle/oradata/SPS_DATA.DBF' resize 4000m

注意：此法在增大存在表数据的表空间时会报错，提示增加失败，建议用下一种方法
（2）增加数据文件的个数

alter tablespace 表空间名 add datafile ‘新增加的数据文件路径’ size 数据文件的大小

例如：

SQL> alter tablespace SPS_DATA add datafile '/home/oracle/app/oradata/snail/SPS_DATA02.dbf' size 2000m

（3）设置表空间自动扩展

alter database datafile ‘要扩展的表空间’ autoextend on next 扩展单元大小 maxsize 最大扩展大小

例如：

SQL> alter database datafile '/home/oracle/app/oradata/snail/SPS_DATA.dbf' autoextend on next 100m maxsize 10000m

备注：方法可以结合使用，尤其当不确定导入文件最终大小时建议使用，如：

SQL> alter tablespace SPS_DATA add datafile '/home/oracle/app/oradata/snail/SPS_DATA02.dbf' size 2000m autoextend on next 200M maxsize 12000M;

对表空间扩充之后可再次执行3.步中查看表空间大小的sql，确认表空间扩充成功。

5、创建临时表空间与数据表空间
创建用户前必须要先创建临时表空间和数据库表空间两个表空间，否则用系统默认的表空间，会引起其他问题。
（1）创建临时表空间

create temporary tablespace 临时表空间名 tempfile ‘临时表空间位置’ size 临时表空间大小autoextend on next 100m maxsize 10240m extent management local;

例如：

SQL> create temporary tablespace SPS_DATA_temp tempfile'/home/oracle/app/oradata/snail/SPS_DATA_temp.dbf' 
size 1024m autoextend on next 100m maxsize 10240m extent management local;

（2）创建数据表空间

参数大致同创建临时表空间

例如：

SQL> create tablespace SPS_DATA logging datafile'/home/oracle/app/oradata/snail/SPS_DATA01.dbf' 
size 1024m autoextend on next 100m maxsize 10240m extent management local;

注意：如果是执行第4步之后，即表空间为扩充而来而不是新建的，则不需要创建数据表空间（但是临时表空间还需要创建——个人观点）

6、创建数据库用户并指定表空间

该用户用于管理即将导入的数据，导入时也切换到该用户进行导入操作（如果用imp命令进行导入，最好此用户名与导出时采用的用户名为同样的用户名,不同的话可能还需要做映射），格式为：

  create user 用户名identified by 用户密码default tablespace 所指定的表空间名 temporary tablespace 临时表空间名;

例如：

SQL> create user abc identified by ABC default tablespace SPS_DATA temporary tablespace SPS_DATA_temp;

7、赋予用户权限
因为要用该用户进行导入操作，故应给予用户的权限至少包括dba、IMP_FULL_DATABASE权限，也有人建议应与导出数据库数据时用户的权限一致。

授权sql：(视具体情况而定)

grant dba,imp_full_database,exp_full_database,connect,resource,create session to 用户名;

例如：

SQL> grant create user,drop user,alter user ,create any view ,drop any view,exp_full_database,
imp_full_database,dba,connect,resource,read,write,create session to abc;

二、导出/导入命令：exp/imp命令
知识扩充：
数据泵导出导入(EXPDP和IMPDP)的作用
（1）实现逻辑备份和逻辑恢复。
（2）在数据库用户之间移动对象。
（3）在数据库之间移动对象
（4）实现表空间搬移。
数据泵导出导入与传统导出导入的区别：
在10g之前，传统的导出和导入分别使用EXP工具和IMP工具，从10g开始，不仅保留了原有的EXP和IMP工具，还提供了数据泵导出导入工具EXPDP和IMPDP.使用EXPDP和IMPDP时应该注意的事项：
EXP和IMP是客户段工具程序，它们既可以在客户端使用，也可以在服务器段使用。
EXPDP和IMPDP是服务端的工具程序，他们只能在ORACLE服务端使用，不能在客户端使用
IMP只适用于EXP导出文件，不适用于EXPDP导出文件;IMPDP只适用于EXPDP导出文件，而不适用于EXP导出文件。

1、导出命令exp
导出与导入分别都有三种方式：
（1）完全模式导出（导入）：
将整个数据库内容导出，但是操作时需要有特殊权限，

exp user/password@server buffer=32000 file=导出的目录 full=y

例如：

exp system/manager buffer=32000 file=d:\iom.dmp full=y

（2）用户模式导出（导入）
将指定用户的所有对象进行导出，例如：

exp iom/iom buffer=32000 file=d:\iom.dmp owner=iom

（3）表模式导出（导入）
将用户的所有表数据进行导出，例如：

exp iom/iom buffer=32000 file=d:\iom.dmp owner=iom tables=(iom)

备注：可以执行exp help=y、imp help=y查看帮助命令，以及执行exp或者imp查看对应版本号。

导出步骤:
首先切换到oracle用户（数据库超级管理员）

[oracle@orac ~]$ su - oracle

根据所需要采用的导出模式进行导出

[oracle@orac~]$exp user/password@server file=file.dmp log=oradb.log full=y compress=y direct=y

COMPRESS参数将在导出的同时合并碎块，尽量把数据压缩到initial的EXTENT里，默认是N，一般建议使用。DIRECT参数将告诉EXP直接读取数据，而不像传统的EXP那样，使用SELECT来读取表中的数据，这样就减少了SQL语句处理过程。一般也建议使用。不过有些情况下DIRECT参数是无法使用的。

其他参数可参照帮助命令，或者其他资料进行学习。在此不一一赘述。

2、导入命令
登录服务器，切换到oracle用户。

[oracle@orac ~]$ su - oracle

执行导入命令：
导入时需要用准备工作中创建的新用户，如：用户名abc，密码ABC

imp user/password@server file=dmp文件路径 log=输出日志路径 full=y ignore=y;

例如：

[oracle@orac ~]$imp abc/ABC file=/home/oracle/iom.dmp log=/home/oracle/iom.log full=y ignore=y;

温馨提示：采用数据泵导入过程经常会遇到问题，建议多查阅资料，总有方法解决。相信每一件事物都有它存在的必要性，问题只是暂时的，成功才是必然的！

三、附加
1、导入导出指定表（表结构和表数据都会导入/导出） https://www.cnblogs.com/527289276qq/p/7824805.html
从源数据库导出：

exp user1/pwd@server1 file=c:\temp\exp.dmp tables=(table1, table2)

导入到目标数据库：

imp user2/pwd@server2 file=c:\temp\exp.dmp tables=(table1, table2)

备注：导入到目标数据库时，先drop目标数据库目标表
2、表与表结构分别导入导出 https://www.cnblogs.com/tzhyy/p/10648518.html
忽略结构，加上ignore=y；忽略数据，加上rows=n
（1）只导入数据、不到导入表结构
不可能只导入数据而不导入表结构这里做了一个变向处理
可以在imp时使用ignore=Y选项就可以了，而不报错。这样就可以实现把数据导入到原来的表里；

imp username/pwd@sid fromuser=xx touser=xx ignore=y;

（2）只导入表结构、不导入数据

imp username/pwd@sid file=all.dmp fromuser=test touser=test rows=n;

（3）只导出表数据，不导出表结构

exp username/pwd@sid file=d:/data/bak.dmp owner=(username) ignore=y;

（4）只导出表结构，不导出表数据
只需在命令行里加一个参数rows=n即可。表示不导出表数据。

exp username/pwd@sid file=d:/data/bak.dmp owner=(username) rows=n;

3、PL/SQL导入导出表(导入所有表、导入指定表，表结构与表数据分别导入导出)
https://www.cnblogs.com/lelehellow/p/6802043.html

4、导入导出命令详解 http://www.talkwithtrend.com/Question/233959?order=asc

转自：https://www.cnblogs.com/alsodzy/p/8675935.html

巴特西

Oracle数据库导入与导出方法简述

最新文章

热门文章