oracle 导入数据库问题总结

最近在工作中遇到要将oracle119导出的dmp文件导入到oracle 12，下面将过程中遇到的问题以及所需知识点总结归纳一下。

一、知识普及

在目标数据库导入时需要创建与导出时相同的用户名（尽量一致），并赋予不低于导出时用户的权限；同时还需创建与原数据库相同的表空间名，若本地数据库已存在相同的表空间，则只能进行表空间扩充。

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

二、导入导出方式

一般数据库导入导出有两种方式，第一种是通过命令（imp/exp）或者（impdp/expdp）,

优点：可避免工具版本差异，效率高（cmd就可以操作），在这次工作中我用到是xshell，这个工具操作不太方便换成了secureCRT这个工具比较好用，操作简单一点推荐使用。

还有一种就是通过工具导如导出，例如oracle自带的oracle SQL developer工具。

三、导入导出方法

用命令导的话可以分为两种，一种是普通导入导出（imp/exp）第二种是数据泵导入导出（impdp/expdp）

区别：

数据泵模式：创建数据泵（名称和地址）（创建一个后即可，以后的数据库.dmp和.log都放这里了）：

(1)创建数据泵（myname：名称，as 地址）

create directory myname as 'D:\companys\fszjxx\datapump';

(2)授予权限（myname：名称，to 用户名称）

grant read,write on directory myname to C##fszfbz;

(3)、查看权限(直接复制查询)

SELECT privilege, directory_name, DIRECTORY_PATH FROM user_tab_privs t, all_directories d WHERE t.table_name(+) = d.directory_name ORDER BY 2, 1;

DIRECTORY=myname即数据泵地址相当于：

DIRECTORY=D:/companys/fszjxx/datapump

数据泵导入

例子(REMAP_SCHEMA=旧用户:新用户)：

directory使用上面创建的数据泵路径！

@echo off

set logfile=fs#zfbz2018-8-17.log

impdp C##fszfbz/fs#zfbz@orcl

DIRECTORY=myname

SCHEMAS=C##fszfbz （用户同名使用这个）

REMAP_SCHEMA=fs#zfbz:C##fszfbz （用户不同名使用这个）

DUMPFILE=EXPDPFS#ZFBZ_NO_LOG20180813.DMP

logfile=%logfile%

pause

数据泵导出

例子(SCHEMAS=导出用户，exclude=排除表)：

directory使用上面创建的数据泵路径！

@echo off

set logfile=fs#zfbz2018-8-17.log

expdp C##guest/guest@orcl

directory=myname

dumpfile=guest20181026dp.dmp

exclude=table:\"in \(\'DB_PEOPLEINFO\',\'DB_FAMILYINFO\'\)\"

SCHEMAS=C##GUEST

logfile=%logfile%

pause

普通模式：

普通导入（full=n：导入当前用户名的数据库）：

导入单个用户例子(fromuser=旧用户,touser=新用户)：

导入数据库.dmp文件和日记.log文件在当前.bat文件运行路径！

@echo off

set logfile=fs#zfbz2018-8-17.log

imp username/pwd@orcl

file=20171108.dmp

fromuser=username

touser=username full=n

log=%logfile%

pause

普通导出

例子(full=n：导出当前用户名的数据库)：

导出数据库.dmp文件和日记.log文件在当前.bat文件运行路径！

@echo off

set logfile=fs#zfbz2018-8-17.log

exp C##guest/guest@localhost:1521/orcl

file=guest20181026.DMP

full=n

log=%logfile%

pause

 四、导如导出模式

1、完全模式导入导出

将整个数据库内容导出，但是操作时需要有特殊权限， exp 用户名/密码buffer=32000file=导出的目录 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)

五、导入导出详情

导入

导入操作（本次示例是多个数据泵文件的导入COSPACE.DMP、WISDOMGOV.DMP）
数据库登陆
plsql 使用sys账号，as sysdba身份登录orcl实例；
1、在被导入的数据库创建操作目录
1.1、在E盘根目录下创建dump文件夹(将数据泵文件都放到此目录(e:\dump)下)
1.2、用系统管理员登录数据库，在SQL执行器中执行如下命令
create directory dump_dir as 'e:\dump'; 

2、创建表空间(sql执行器中执行如下命令)
2.1、创建COSPACE表空间
create tablespace COSPACE  
logging  
datafile 'E:\app\Administrator\oradata\orclWG\COSPACE.DBF'    --注：数据库实例当时创建时指定的路径
size 50m  
autoextend on  
next 50m maxsize 20480m  

extent management local;

2.2、创建WISDOMGOV表空间
create tablespace WISDOMGOV 
logging  
datafile 'E:\app\Administrator\oradata\orcl\WISDOMGOV.DBF'   --注：数据库实例当时创建时指定的路径
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;

3、创建用户(sql执行器中执行如下命令)
3.1、创建COSPACE用户
create user COSPACE identified by lilo123  
default tablespace  COSPACE;

3.2、创建WISDOMGOV用户
create user WISDOMGOV identified by lilo123  
default tablespace  WISDOMGOV;

4、用户赋权(sql执行器中执行如下命令)
4.1、赋权给COSPACE
grant connect,resource,dba to COSPACE;

4.2、赋权给WISDOMGOV
grant connect,resource,dba to WISDOMGOV;

5、将数据泵使用目录赋值给用户(sql执行器中执行如下命令)
5.1、grant read,write on directory dump_dir to COSPACE;
5.2、grant read,write on directory dump_dir to WISDOMGOV;

6、执行导入数据（切换到数据泵存放的路径下，执行cmd命令行如下）
6.1、impdp COSPACE/123456@orclWG directory=dump_dir dumpfile=COSPACE.DMP
6.2、impdp WISDOMGOV/123456@orcl directory=dump_dir dumpfile=WISDOMGOV.DMP

7、执行创建同义词脚本

impdp SHANXIDW/SHANXIDW@orcl rows=y ignore=y

file=partner.dmp

from user=SCMS touser=LNSCMS_D logfile=imp1.log  full=y 

（IMPDP导入的时候 ：用参数table_exists_action=replace 进行删除后覆盖；

table_exists_action选项：{skip 是如果已存在表，则跳过并处理下一个对象；append是为表增加数据；truncate是截断表，

然后为其增加新数据；replace是删除已存在表，重新建表并追加数据}）

2、导出

导出操作(导出多个数据泵文件)

数据库登陆
plsql 使用sys账号，as sysdba身份登录orcl实例；
1、创建操作目录
1.1、在E盘根目录下创建dump文件夹(数据泵导出存放的路径)
1.2、用系统管理员登录数据库，在SQL执行器中执行如下命令
create directory dump_dir as 'e:\dump'; 
1.3、将该目录的使用权限赋给用户，执行如下命令（SQL执行器执行）
1.3.1、赋值给cospace用户
grant read,write on directory dump_dir to cospace;
1.3.2、赋值给wisdomgov用户
grant read,write on directory dump_dir to wisdomgov;
2、导出数据(切换到数据泵存放的路径下，执行cmd命令行如下)
2.1、导出cospace数据
expdp cospace/123456@orclWG directory=dump_dir file=COSPACE.dmp schemas=cospace
2.1、导出wisdomgov数据
expdp wisdomgov/123456@orcl  directory=dump_dir file=WISDOMGOV.dmp schemas=wisdomgov

六、带三方工具（oracle SQL developer）

1、导出格式介绍

 （1）Dmp格式：.dmp是二进制文件，可跨平台，还能包含权限，效率好， 
 （2）Sql格式：.sql格式的文件，可用文本编辑器查看，通用性比较好，效率不如第一种，适合小数据量导入导出。尤其注意的是表中不能有大字段 （blob,clob,long），如果有，会报错 
 （3）Pde格式：.pde格式的文件，.pde为PL/SQL Developer自有的文件格式，只能用PL/SQL Developer工具导入导出，不能用文本编辑器查看。 

2、导出方法

（1）导出建表语句（包括存储结构） 
 导出步骤tools ->export user object，选择要导出的对象，导出.sql文件

（2）导出数据文件 
  导出步骤tools ->export tables，选择要导出的表及导出的格式进行导出，导出为dmp格式

3、导入方法

（1）导入建表语句 
  导入步骤tools->import tables->SQL Inserts 导入.sql文件 

（2）导入数据 
   tools->import talbes，然后再根据导出的数据格式选择导入dmp文件，或者sql文件，或者pde文件。

注意：在导入前将之前的表删除，在工具导入大量数据的时候，需要大量的时间。