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


Error Description:
Import using imp utility is failing with following error.

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

Solution Description:
This is because of lack of privilege for importing the dump file. The export dump would have taken by a DBA privileged user but the user is trying to import the dump should have at least IMP_FULL_DATABASE privilege or a DBA privileged user can import the dump easily. You can give the access using the following syntax.

Syntax: grant IMP_FULL_DATABASE to username;

Example:


imp file=exp_schema.dmp log=imp_exp_schema.log fromuser=scott touser=test

Import: Release 11.2.0.2.0 - Production on Mon Oct 15 02:03:41 2012

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

Username: test
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes

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> conn test/test
Connected.

SQL> select * from session_roles;

no rows selected

SQL> conn / as sysdba
Connected.
SQL> grant IMP_FULL_DATABASE to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> select * from session_roles;

ROLE
------------------------------
IMP_FULL_DATABASE
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_EXECUTE_ROLE

Trying the import after granting the privilege and it worked.

imp file=exp_schema.dmp log=imp_exp_schema.log fromuser=scott touser=test

Import: Release 11.2.0.2.0 - Production on Mon Oct 15 02:06:34 2012

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

Username: test/test

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes

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

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

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into TEST
. . importing table                      "BL_TEST"      64008 rows imported
Import terminated successfully without warnings.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google