问题描述
[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;