IMP-00013: only a DBA can import a file exported by another DBA解决

问题描述

[oracle@aud oradata]$ imp aud/xxxx file=/u01/app/oracle/oradata/audit_log.dmp tables=audit_log

Import: Release 11.2.0.4.0 - Production on Wed Jun 3 10:54:17 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully

问题原因

查询错误号IMP-00013是因为当前用户aud不具有导入由其他用户导出数据的权限，因为audit_log.dmp在源库是由sys用户导出的，在目标库需要用aud导入。

解决办法

• 方法1,使用DBA用户导入,添加fromuser和touser参数（未验证）

$ imp \'aud/xxxx as sysdba\' =/u01/app/oracle/oradata/audit_log.dmp tables=audit_log fromuser=sys touser=aud ignore=y log=imp.log

• 方法2,赋予该用户imp_full_database的权限

SQL> grant imp_full_database to aud;

注：如果源DB中USER01用户只有exp_full_database权限，即使只是导出了自己的表，在导入时也必须要有imp_full_database的权限。

[oracle@aud oradata]$ imp nsfcaud/nsfcaud_rac11g file=/u01/app/oracle/oradata/audit_log.dmp tables=audit_log

Import: Release 11.2.0.4.0 - Production on Wed Jun 3 11:05:45 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SYS, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses AL32UTF8 character set (possible charset conversion)
. importing SYS's objects into AUD
. importing SYS's objects into AUD
. . importing table                    "AUDIT_LOG"   22491788 rows imported
Import terminated successfully without warnings.

收回权限

SQL> revoke exp_full_database,imp_full_database from XXXX;