IMP-00013 问题及解决方法

昨天在生产上出现了一个问题，客户的dba发现,使用临时用户导出了一个dump之后，无法导入测试环境，报错是常见的

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

这个问题一般碰到的处理方法就是在 给目标环境的用户赋予dba权限，或者细粒度一些，赋予imp_full_database的权限，就能解决。

我检查了一下目标环境，是11g的库
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.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

SQL> show parameter insta
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
active_instance_count integer
cluster_database_instances integer 1
instance_groups string
instance_name string TEST02
instance_number integer 0
instance_type string RDBMS
open_links_per_instance integer 4
parallel_instance_group string

parallel_server_instances integer 1

SQL> select * from dba_sys_privs where grantee = 'TESTAPP';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TESTAPP ON COMMIT REFRESH NO
TESTAPP CREATE TABLE NO
TESTAPP CREATE TYPE NO
TESTAPP GLOBAL QUERY REWRITE NO
TESTAPP QUERY REWRITE NO
TESTAPP CREATE ANY SYNONYM NO
TESTAPP UNLIMITED TABLESPACE NO

7 rows selected.
--查看用户的role，已经有dba了。
SQL> select * from dba_role_privs where grantee ='TESTAPP';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TESTAPP TIGER_ROLE NO YES
TESTAPP TESTAPP_SEL YES NO
TESTAPP TESTAPP_ALL YES NO
TESTAPP DBA NO NO


查看其它的dba用户，发现default字段是YES,查看metalink ID 949279.1，里面也给出了类似问题的解决方法。
SQL> select * from dba_role_privs where grantee ='N1';


GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
N1 DBA NO YES

CAUSE

The granted roles are not activated (DEFAULT_ROLE is NO).

SOLUTION

First activate the roles with:

SQL> alter user usr001 default role all;

再次尝试。
SQL> ALTER USER TESTAPP DEFAULT ROLE ALL;
User altered.

激活后，再次验证，发现dba权限已经default了。

SQL> select * from dba_sys_privs where grantee = 'TESTAPP';


GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TESTAPP ON COMMIT REFRESH NO
TESTAPP CREATE TABLE NO
TESTAPP CREATE TYPE NO
TESTAPP GLOBAL QUERY REWRITE NO
TESTAPP QUERY REWRITE NO
TESTAPP CREATE ANY SYNONYM NO
TESTAPP UNLIMITED TABLESPACE NO
7 rows selected.

SQL> select * from dba_role_privs where grantee ='TESTAPP';


GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TESTAPP TIGER_ROLE NO YES
TESTAPP TESTAPP_SEL YES YES
TESTAPP TESTAPP_ALL YES YES
TESTAPP DBA NO YES

再次导入，就没有碰到问题。