ORACLE数据导入导出

一、导入导出方式

1.普通导入导出

imp导入

imp 用户名/密码@数据库IP:端口号/orcl FILE=导入的目标文件名称 TABLES=表名(多个表时用逗号隔开)

imp TJNS_DS/bangsun@10.10.1.54:1521/orcl FILE=exp.dmp TABLES=（USER_INFO,POLICY_INFO）;

exp导出

exp 用户名/密码@数据库IP:端口号/orcl FILE=导出的目标文件名称 TABLES=表名(多个表时用逗号隔开)

exp TJNS_DS/bangsun@10.10.1.54:1521/orcl FILE=exp.dmp TABLES=（USER_INFO,POLICY_INFO）;

命令详解

file --导出文件路径及文件名，文件后缀为.dmptables --要导出的表名，导出多个表时表名要写在括号内并用逗号隔开ignore --赋值为y时，如果表已经存在，会跳过，否则将会报错owner --导出时使用，用于导出所属用户的全部内容full --赋值为y时，导出完整的数据库fromuser --导入时使用，指源数据库用户名touser --导入时使用，值要导入的数据库用户名

2.数据泵导入导出

​ impdp 用户名/密码@数据库IP:端口号/orcl DUMPFILE=导出的目标文件名称 TABLES=表名(多个表时逗号隔开)

​impdp TJNS_DS/pass@10.10.1.54:1521/orcl DUMPFILE=export.dmp TABLES=USER_INFO,POLICY_INFO

expdp导出

​ expdp 用户名/密码@数据库IP:端口号/orcl DUMPFILE=导出的目标文件名 TABLES=表名(多个表时用逗号隔开)

​expdp TJNS_DS/pass@10.10.1.54:1521/orcl DUMPFILE=export.dmp TABLES=USER_INFO,POLICY_INFO;

命令详解

directory --导出逻辑目录，该名称指向字典DBA_DIRECTORIES中该名称对应的路径dumpfile --导出文件的名称，后缀为.dmplogfile --记录导出过程日志，后缀为.logtables|schemas|tablespaces|full --分别表示导出表、模式（用户）、表空间、全库remap_schema=from_schema:to_schema --改变用户所属,从哪个用户来，要到哪个用户去remap_tablespace=from_tbs:to_tbs --改变表空间table_exists_action={skip|append|replace|truncate} --表存在时的处理：跳过、补充、替换、清空SKIP:当表存在时，什么操作都不做，直接跳过APPEND:当表存在时，在现有数据的基础之上补充数据REPLACE:当表存在时，替换表结构和数据，相当于删除表后又重建插数据TRUNCATE:当表存在时，清空表，再插入数据

3.两种方式的区别

exp和imp可用于所有的版本，而数据泵方式只能用于10g以及之后的版本

速度方面，数据泵方式要比传统的imp要快很多

指定导出文件路径方面，传统方式导出可以指定任意有权限的目录，而数据泵导方式必须使用逻辑目录

导入时如果要覆盖已存在的表，只能用数据泵方式，传统方式只能跳过

二、数据泵方式完整流程

1.创建数据库表空间及其dbf文件

CRATE TABLESPACE "DB_USER" DATAFILE '/home/oracle/user_db.dbf' SIZE 209710 AUTOEXTEND ON NEXT 8192 MAXSIZE 31231314M LOGGING ONLINE PERMANTET BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT MANAGEMENT AUTO;

2.表空间dbf文件扩容

ALTER DATABASE DATAFILE '/home/oracle/userDB.dbf' RESIZE 8160608256; --文件扩容ALTER DATABASE DATAFILE '/home/oracle/userDB.dbf' autoentend on maxsize unlimited; --不限制大小

3.创建数据库用户并指定表空间

create user DB_USER IDENTIFIED BY "Password" default tablespace DB_USER temporary tablespace TEMP；

4.为用户授权

grant read,write on directory SYS.DMPFILE to DB_USER;grant connect to DB_USER;grant resource to DB_USER;grant create database link to DB_USER;grant create table to DB_USER;grant create view to DB_USER;

5.创建逻辑目录并授权

create or replace directory DUMP_DIR as '/home/online/oracle_file'; --创建逻辑目录grant read,write on directory DUMP_DIR to DB_USER; --授予用户逻辑目录操作权限

6.表空间维度导出数据库内容

expdp DB_USER/online @10.1.1.103:1521/orcl DIRECTORY=DUMP_DIR DUMPFILE=db_user.dmp

7.将dmp文件内容导入当前数据库

impdp DB_USER/online @10.1.1.103:1521/orcl DIRECTORY=DUMP_DIR DUMPFILE=db_user.dmp REMAP_TABLESPACE=USER:DB_USER REMAP_SCHEMA=USER:DB_USER TABLE_EXISTS_ACTION=REPLACE

三、注意事项

使用数据泵方式进行表空间维度导入时，要注意原表空间和当前表空间是否一致，如果不一致导入会报错

导入时要判断当前表空间的dbf容量是否足够，如果空间不够需要进行dbf文件扩容后再进行导入

数据泵方式如果数据库的密码中有@字符，需要用’ ''password" ’ 将密码内容包起来,先用单引号,再用双引号

数据泵方式导出时要先确认逻辑目录剩余空间大小，以防空间不足导致导出失败，同时要确认导出的逻辑目录对应的路径，找到导出的dmp文件

四、误删dbf文件后报错处理

1.报错情景和内容

​ 如果直接删除数据库的dbf文件，如果oracle不重启就没有问题，如果oracle被杀掉再启动，oracle就会报错。Oracle在启动时要将方案中原有配置信息装载进入，装载过程中配置中有文件未找到，所以就报出错误;误删dbf文件之后,数据库不会有问题,但是如果数据库进行重启的时候会报错，报错内容如下：

ORA-01157: 无法标识/锁定数据文件 5 - 请参阅 DBWR 跟踪文件ORA-01110: 数据文件 5: ‘F:/ORACLE/TEST01.DBF’ --说明是标号为5的dbf文件被删除,需要先恢复再脱机

2.问题解决

SQL>shutdown normal ； --关闭数据库连接

SQL>startup mount;

SQL>alter database open; --打开数据库查看有哪些文件已经删除，但是在数据库控制文件中还存在记’’

如果误删了dbf文件,会出现以下报错:

ORA-01157: 无法标识/锁定数据文件 5 - 请参阅 DBWR 跟踪文件

ORA-01110: 数据文件 5: ‘F:/ORACLE/TEST01.DBF’

说明是标号为5的dbf文件被删除,需要先恢复再脱机

SQL> alter database create datafile 5

SQL> alter database datafile 5 offline drop;

执行完以上步骤后在进行重启,如果还报错,说明删除的文件不止一个,重复上面的操作直到不报错问题就解决了

3.删除表空间和dbf文件的正确命令

DROP TABLESPACE DB_USER INCLUDING CONTENTS AND DATAFILES;