当前位置: 首页 > exp/imp, ORA > 正文

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

今天在11.2.0.3环境中用scott导出表,同一个用户再导入到同一个数据库中,切报了下面的错误

[oracle11g@rhel4 sql]$ imp scott/oracle file='/tmp/test.dmp' tables='test_1';

Import: Release 11.2.0.3.0 - Production on Fri Apr 19 09:51:15 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.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

测试环境:DB:11.2.0.3  OS:RHEL 4.8

1,使用SCOTT用户导入一张表

[oracle11g@rhel4 sql]$ exp scott/oracle file='/tmp/test.dmp' tables='test_1';

Export: Release 11.2.0.3.0 - Production on Fri Apr 19 09:50:21 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                         TEST_1          0 rows exported
Export terminated successfully without warnings.

2,删除表后,马上导入

SQL> drop table scott.test_1;

Table dropped.
[oracle11g@rhel4 sql]$ imp scott/oracle file='/tmp/test.dmp' tables='test_1';

Import: Release 11.2.0.3.0 - Production on Fri Apr 19 09:51:15 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.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

3,提示只有DBA权限才可能导入有DBA权限导出的文件,使用SYSTEM用户导入成功

[oracle11g@rhel4 sql]$ imp system/oracle file='/tmp/test.dmp' tables='test_1' fromuser=scott touser=scott

Import: Release 11.2.0.3.0 - Production on Fri Apr 19 09:52:06 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.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 SCOTT, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table                       "TEST_1"          0 rows imported
Import terminated successfully without warnings.

4,查看用户SYS权限与ROLE

使用具有DBA权限的用户能正常导入

SQL> @user_sys_priv.sql

Enter Search User Name (i.e. SCOTT) : scott

USERNAME             PRIVILEGE
-------------------- ----------------------------------------
scott                ADMINISTER RESOURCE MANAGER
scott                ADMINISTER SQL MANAGEMENT OBJECT
scott                BACKUP ANY TABLE
scott                CREATE SESSION
scott                CREATE TABLE
scott                EXECUTE ANY PROCEDURE
scott                EXECUTE ANY TYPE
scott                READ ANY FILE GROUP
scott                RESUMABLE
scott                SELECT ANY SEQUENCE
scott                SELECT ANY TABLE
scott                UNLIMITED TABLESPACE

12 rows selected.

SQL> @user_role.sql

Enter Search User Name (i.e. SCOTT) : scott

                                                                                  ADMIN
GRANTEE                                  GRANTED_ROLE                             OPTION DEF      LEVEL
---------------------------------------- ---------------------------------------- ------ --- ----------
SCOTT                                    CONNECT                                  NO     YES          1
SCOTT                                    EXP_FULL_DATABASE                        NO     YES          1
SCOTT                                    RESOURCE                                 NO     YES          1
EXP_FULL_DATABASE                        EXECUTE_CATALOG_ROLE                     NO     YES          2
EXP_FULL_DATABASE                        SELECT_CATALOG_ROLE                      NO     YES          2
EXECUTE_CATALOG_ROLE                     HS_ADMIN_EXECUTE_ROLE                    NO     YES          3
SELECT_CATALOG_ROLE                      HS_ADMIN_SELECT_ROLE                     NO     YES          3

这里发现用户并没有DBA权限,在导入/导出方面具有exp_full_database的权限,由于想到了imp_full_database这个权限,授予imp_full_database权限再次导入

SQL> drop table scott.test_1
  2  ;

Table dropped.

SQL> grant imp_full_database to scott;

Grant succeeded.

SQL> !imp scott/oracle tables=test_1 file='/tmp/test.dmp';

Import: Release 11.2.0.3.0 - Production on Fri Apr 19 09:55:13 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.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
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                       "TEST_1"          0 rows imported
Import terminated successfully without warnings.

导入正常,此用户并没有DBA的权限,然后ORACLE提示具有DBA权限,被误导了。

下面是metalink上面关于此问题的描述

EXP-23 or IMP-13 When Performimg Full Export or Import [ID 949279.1]
How To Resolve Oracle Database Import Error IMP-00013 [ID 1290022.1]

本文固定链接: http://www.htz.pw/2013/04/19/imp-00013-only-a-dba-can-import-a-file-exported-by-another-dba.html | 认真就输

该日志由 huangtingzhong 于2013年04月19日发表在 exp/imp, ORA 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: IMP-00013: only a DBA can import a file exported by another DBA | 认真就输
关键字: , ,