oracle数据库数据导入导出步骤(入门)

时间:2024-01-13 16:51:56

oracle数据库数据导入导出步骤(入门)

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

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

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

1. 登录服务器

 用Xshell或者secureCRT工具都可

2. 查询磁盘空间是否足够大

 执行df -h或者df -H命令进行查询,若可用空间不足则更换新的目标环境再继续其他操作。

[oracle@orac ~]$ df -h
  • 1

3. 查询表空间详情

 ①使用终端登录,依次执行命令: 
[oracle@orac ~]$ su - oracle (切换到oracle用户(linux的一个用户名)) 

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

 [oracle@orac ~]$  /home/oracle/app/oradata
[oracle@orac ~]$ mkdir snail
  • 1
  • 2

  ②登录数据库

 (注意/与as之间要有空格)
[oracle@orac ~]$ sqlplus / as sysdba
  • 1
  • 2
  • 3

   执行sql语句

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;
  • 1

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

oracle数据库数据导入导出步骤(入门)

  也可以通过第三方工具登录数据库执行上述sql语句(以下几步也同样可以) 
  *注:如果要导入的数据库表空间名与当前已有表空间名相同,则无需新建表空间(也不可以再建),但是必须确定已有表空间大小充足,或者已经设置为自动增加并且自动增加的最大值足够大,那么就不需要进行表空间扩充,直接使用该表空间级即可,跳过第四步。 
 反之如果没有该名称的表空间,或者表空间大小不足以存放要导入的数据时,需要对该表空间进行扩充,执行第四步。

4. 表空间扩充

 对表空间进行扩充有多种方法,简单介绍其中几个常用方法: 
 ① 直接增大表空间的大小: 
   先查看表空间中数据文件存放的位置

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

  确定数据文件位置后,执行命令: 
  alter database datafile ‘数据文件路径’ resize 要增大的大小 
  例如:

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

   注意:此法在增大存在表数据的表空间时会报错,提示增加失败,建议用下一种方法 
 ② 增加数据文件的个数 
  alter tablespace 表空间名 add datafile ‘新增加的数据文件路径’ size 数据文件的大小 
  例如:

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

 ③ 设置表空间自动扩展 
  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
  • 1

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

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

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

5. 创建临时表空间与数据表空间

 创建用户前必须要先创建临时表空间和数据库表空间两个表空间,否则用系统默认的表空间,会引起其他问题。 
 ①创建临时表空间 
  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;
  • 1

 ②创建数据表空间 
  参数大致同创建临时表空间 
  例如:

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;
  • 1

  注意:如果是执行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;
  • 1

7. 赋予用户权限

 因为要用该用户进行导入操作,故应给予用户的权限至少包括dba、IMP_FULL_DATABASE权限,也有人建议应与导出数据库数据时用户的权限一致。 
 授权sql:(视具体情况而定)

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

 例如:

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;
  • 1

二、采用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 用户名/密码buffer=32000file=导出的目录 full=y 
  例如:

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

 ②用户模式导出(导入) 
  将指定用户的所有对象进行导出,例如:

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

 ③表模式导出(导入) 
  将用户的所有表数据进行导出,例如:

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

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

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

[oracle@orac ~]$ su - oracle   
  • 1

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

[oracle@orac~]$exp iom/iom file=iom.dmp log=oradb.log full=y compress=y direct=y   
  • 1

  COMPRESS参数将在导出的同时合并碎块,尽量把数据压缩到initial的EXTENT里,默认是N,一般建议使用。DIRECT参数将告诉EXP直接读取数据,而不像传统的EXP那样,使用SELECT来读取表中的数据,这样就减少了SQL语句处理过程。一般也建议使用。不过有些情况下DIRECT参数是无法使用的。 
其他参数可参照帮助命令,或者其他资料进行学习。在此不一一赘述。

2. 导入命令

 登录服务器,切换到oracle用户。

[oracle@orac ~]$ su - oracle 
  • 1

 执行导入命令: 
  导入时需要用准备工作中创建的新用户,如:用户名abc,密码ABC 
  imp 用户名/密码 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

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

三、采用第三方工具(以PLSQL为例)

1. 导出格式介绍

 ① Dmp格式:.dmp是二进制文件,可跨平台,还能包含权限,效率好, 
 ② Sql格式:.sql格式的文件,可用文本编辑器查看,通用性比较好,效率不如第一种,适合小数据量导入导出。尤其注意的是表中不能有大字段 (blob,clob,long),如果有,会报错 
 ③ Pde格式:.pde格式的文件,.pde为PL/SQL Developer自有的文件格式,只能用PL/SQL Developer工具导入导出,不能用文本编辑器查看。 
备注:虽然dmp格式为最优选,但是不容易实现,原因有俩点:首先此格式需要安装oracle完整版,因为导出时需要选择对应exp.exe与imp.exe,而且导出是安装的版本要与要导出数据的数据库版本一致,同理导入时也要对应,否则会出现版本不一致的问题(查阅资料中介绍的,个人没有证实);其次此格式的导出经常会遇到导出过程一闪而过,但是导出不成功的情况,原因未知(可以查阅环境变量配置ORACLE_HOME配置正确,个人尝试多次,仍有问题,最后无奈选取导出为pde格式)。

2. 导出方法

登录plsql工具,所使用用户为源数据库有导出权限(exp_full_database,dba等)的用户。 
 ①导出建表语句(包括存储结构) 
 导出步骤tools ->export user object,选择要导出的对象,导出.sql文件,如下图: 
oracle数据库数据导入导出步骤(入门)
  等待导出完成

 ②导出数据文件 
  导出步骤tools ->export tables,选择要导出的表及导出的格式进行导出。 
  导出为dmp格式,如下图: 
  
oracle数据库数据导入导出步骤(入门)
  
  导出为sql格式,如下图: 
  
oracle数据库数据导入导出步骤(入门)
  
  导出为pde格式,如下图: 
  
oracle数据库数据导入导出步骤(入门)
备注:采用第三方工具导出导入整个数据库的话,耗时较长,一定要有足够的时间来操作(数据量大的话需要好几个小时)

3. 导入方法

登录plsql工具,使用之前准备工作所创建的用户。 
 步骤类似导出方法 
 ①导入建表语句 
  导入步骤tools->import tables->SQL Inserts 导入.sql文件 
 ②导入数据 
   tools->import talbes,然后再根据导出的数据格式选择导入dmp文件,或者sql文件,或者pde文件。 
  备注:导入之前最好把以前的表删除,当然导入另外数据库除外。 
     另外导入时当发现进度条一直卡在一个点,而且导出的文件不再增大时,甚至是提示程序未响应,千万不要以为程序卡死了,这个导入导出就是比较缓慢,只要没有提示报错,或者导入完成就不要停止程序。 
  
  
  
  

参考文献:

  1. Oracle - 数据库的实例、表空间、用户、表之间关系: 
    http://www.cnblogs.com/adforce/p/3312252.html 
    2.PL/SQL Developer导入导出数据库方法: 
    http://jingyan.baidu.com/article/d5a880eb69ff7313f047cc5f.html 
    3.linux下创建oracle表空间: 
    http://blog.sina.com.cn/s/blog_62192aed01018aep.html 
    4.plsql连接64位数据库及plsql导出表闪现错误: 
    http://201405221449.iteye.com/blog/2241714 
    5.oracle安装包官网下载: 
    http://www.oracle.com/technetwork/topics/winsoft-085727.html 
    6.ORACLE 表空间扩展方法: 
    http://www.2cto.com/database/201110/109669.html 
    7.Exp/imp命令详解: 
    http://blog.csdn.net/caijing3210/article/details/7528788 
    http://www.cnblogs.com/yugen/archive/2010/07/25/1784763.html 
    http://www.cnblogs.com/whsa/p/3975817.html

  

备注:该博客为学习交流之用,有内容为参考其他博客,如有侵权请速与本人联系,本人将第一时间处理,谢谢合作!

版权声明:本文为博主原创文章,转载需要注明出处 https://blog.csdn.net/Cow_cz/article/details/70229929