查阅代码整理(四)之oracle导入导出（空表及expdp导出的导入）

由于本地连接公司服务器上的数据库影响因素太大，网速、网络环境等。因此就需要对数据库进行导出（开发的库并非生产库，当然生产库导出也不够权限不是）。

先说博主遇到的数据库导入失败（真的失败和达不到预期效果都算失败吧）情况：

1 导出的表忘记空表处理，导致数据库导出后，由于缺少空表无法使用；

2 用expdp导出，自动创建了用户名和默认表空间，用impl无法导入，要用impdp导入。

针对以上问题博主整理了自己平时使用的方式，建议收藏方便今后查阅。

1  导出dmp文件（带空表处理）

 

1.1 处理空表

对已经创建的表的处理

1）在sql窗口下点输出修改缓冲区大小为100000，再执行sql；

-- Created on 2020/07/12 by LIZHIHUI

declare

  -- Local variables here

  i integer;

begin

  -- Test statements here

  for c in (select * from user_all_tables)

  loop

    dbms_output.put_line('alter table '||c.table_name||' allocate extent;');

  end loop;

end;

 

2）执行完后点输出把输出窗口里的ctrrl+a全选并复制，然后在命令窗口粘贴刚才的内容执行。如下图：

执行完毕后就可以导出空表了。

1.2 导出dmp文件

1）进入命令窗口

2）输入导出命令

 

exp  数据库名/密码@ip：端口/database  file=d:/xx.dmp

例如：

exp  QHRS/1@127.0.0.1:1521/orcl file=d:/qhrs0908_1.dmp

查看是否已经有空表导出

这样就可以导出成功。

2  导入dmp文件

 

2.1 已有用户导入

依旧在命令窗口执行命令导入

语法：imp 用户名/密码@数据库的ip地址/数据库实例 file=’dmp文件所在的根目录’ full=y;

例如：

imp tptz0601/0@127.0.0.1:1521/orcl file='d:/tptz0602.dmp' full =y;

2.2  没有用户需要先创建用户再导入

2.2.1连接数据库

命令窗口运行

 sqlplus/nolog

进行sql对话框，输入命令连接数据库。

语法：conn  数据库名/密码@ip：端口/database；

例如：conn zyys/zyys@127.0.0.1:1521/orcl    

2.2.2 赋予权限

输入sqlplus / as sysdba 登录Oracle数据库；

创建新用户，最好是按照项目中的数据库链接名称来命名，

语法：create user 用户名 identified by 密码；

2.2.3 赋予新用户权限

grant connect,resource,dba to 用户名，

注：connect是赋予连接数据库的权限，resource是赋予用户只可以创建实体但是没有创建数据结构的权限，dba是赋予用户所有权限，这个地方如果不赋dba权限，会导致权限级别不够（其实一个dba权限就够了，不过个人习惯）；

2.2.4 查看是否赋予成功

赋予权限完毕之后，连接数据库，看能否连接成功。

语法：conn  数据库名/密码@ip：端口/database；

例如：conn zyys/zyys@127.0.0.1:1521/orcl    

连接成功会提示已连接；

2.2.5 导入 

exit退出SQL命令，然后执行导入命令，

语法：imp 用户名/密码@数据库的ip地址/数据库实例 file=’dmp文件所在的根目录’ full=y;

 

3 用expdp导出的数据的导入

用expdp导出的数据的导入，imp会导不进去，要用impdp导入。具体方法如下：

3.1 win运行cmd命令

3.2 连接数据库

执行：

sqlplus system/manager as sysdba

3.3 创建和导出数据库一样的用户（创建其他的无法导入，希望各位有好的解决方法留言分享，谢谢！）

执行：

 create user POM_UAT identified by 1;

注：建立和导出数据库一样的用户，这里用POM_UAT，by 1，1是设置的密码，太复杂的密码无法创建用户。
 

3.4 创建和导出用户一致的表空间

执行  

alter user POM_UAT default tablespace POM_DATA;

创建和导出用户POM_UAT一致的表空间 POM_DATA。

3.5 赋予dba权限

执行

grant dba to POM_UAT;

 赋予用户POM_UAT的dba权限。

3.6 创建目录变量

执行

create directory pom_dir as 'D:\POM_UAT';

注意：创建目录变量（文件夹）create directory 目录名 as ‘D:\POM_UAT’; 需要手动创建D:\POM_UAT并把导出的dmp文件放进去。

3.7  赋予用户可读写目录变量的权限

执行

grant read,write on directory pom_dir to POM_UAT;  

3.8 退出

执行

quit

 

3.9 导入数据

语法：impdp 创建的用户名/创建的用户名密码 directory=目录变量 dumpfile=*数据库名.dmp full=y table_exists_action=replace

多个导入语法：impdp 创建的用户名/创建的用户名密码 directory=目录变量 dumpfile=*数据库名_%U.dmp full=y table_exists_action=replace

例如：

impdp POM_UAT/1 directory=pom_dir dumpfile=POM_UAT_20191122_%U.dmp full=y table_exists_action=replace

注意：如果导出的是多个文件，需要用POM_UAT_20191122_%U.dmp代替