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

最新推荐文章于 2021-04-08 21:49:41 发布

原创最新推荐文章于 2021-04-08 21:49:41 发布 · 2.8k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

笔记同时被 2 个专栏收录

164 篇文章

订阅专栏

Oracle

129 篇文章

订阅专栏

本文详细解析了在Oracle数据库中，用户在尝试导入由其他用户导出的数据文件时遇到的权限错误IMP-00013。阐述了错误产生的原因，并提供了两种解决方案：一是使用DBA用户进行导入并指定fromuser和touser参数；二是为当前用户授予imp_full_database权限。

问题描述

[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;