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 --导出文件路径及文件名,文件后缀为.dmp tables --要导出的表名,导出多个表时表名要写在括号内并用逗号隔开 ignore --赋值为y时,如果表已经存在,会跳过,否则将会报错 owner --导出时使用,用于导出所属用户的全部内容 full --赋值为y时,导出完整的数据库 fromuser --导入时使用,指源数据库用户名 touser --导入时使用,值要导入的数据库用户名
2.数据泵导入导出
- impdp 导入
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 --导出文件的名称,后缀为.dmp
logfile --记录导出过程日志,后缀为.log
tables|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;