Oracl数据库expdp/impdp导入导出遇到的一系列问题解决：ORA-01110、ORA-31684、ORA-39151、、ORA-01940、ORA-00031

在将开发数据库的数据导入测试数据库时遇到的一些问题分享

报错信息

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 201: '/oracle/oradata/ora/temp01.dbf'
ORA-06512: at "SYS.DBMS_LOB", line 741
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5412
ORA-06512: at line 1

解决方法
需要新建temp文件

删除原本的临时文件
alter database  tempfile '/oracle/oradata/ora/temp01.dbf' drop;
重新添加临时数据文件：
alter tablespace temp add tempfile '/oracle/oradata/ora/temp01.dbf' size 100M reuse;

报错信息

ORA-31684: Object type DB_LINK:"TEST"."PRD" already exists

ORA-31684: Object type DB_LINK:"TEST"."CABLE" already exists

ORA-31684: Object type DB_LINK:"TEST"."D31" already exists

ORA-31684: Object type DB_LINK:"TEST"."DEV" already exists
ORA-39151: Table "TEST"."TBLTO" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "TEST"."TOOLS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "TEST"."STRANS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "TEST"."TBLTP" exists. All dependent metadata and data will be skipped due to table_exists_action of skip


SQL> drop user demo cascade;
drop user demo cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected 

如果只有ora-39151的报错那么在导入的命令后面加上一个参数就可以了table_exists_action=replace，这个根据情况，如果只需要导入的表信息就替换原有的表，如果想保留就将replace改为skip

在这还有其他对象的已存在的报错，那么就删除该用户这样就能把测试库的所有对象删掉

SQL> drop user demo cascade;
drop user demo cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

删除用户报错，显示正在连接，那就杀掉这个进程

先查出这个用户对应的sid和serial#执行下列命令
SQL> select saddr,sid,serial#,paddr,username,status from v$session where username = 'DEMO';

SADDR                   SID    SERIAL# PADDR
---------------- ---------- ---------- ----------------
USERNAME
--------------------------------------------------------------------------------
STATUS
--------
000000007B655420       1717      57759 000000007D9F4580
DEMO
ACTIVE


SQL> alter system kill session '1717,57759';
alter system kill session '1717,57759'
*
ERROR at line 1:
ORA-00031: session marked for kill

执行报错，那就根据sid和serial#查出操作系统对应的进程号，直接在操作系统层面杀掉

SQL> select c.spid from v$process c where c.addr=(select b.paddr from v$session b where b.sid= '1717' and b.serial#= '57759');

SPID
------------------------
5418


kill -9 5418

然后再导入就不会报已存在的错误了