Solved

impdp ora-1031

Posted on 2009-05-09
8
1,862 Views
Last Modified: 2013-12-18
Hi
I am trying to imp using network link

i am getting a error ora-1031

ORA-31693: Table data object "SCOTT"."MKT" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-02063: preceding line from db_link

But i can select * from MKT@db_link

could some one pls advice,thx
sushil
0
Comment
Question by:sushilt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 24346927
Can u post the import log file?
0
 

Author Comment

by:sushilt
ID: 24346938
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "NAV"."SYS_IMPORT_TABLE_01":  nav/******** directory=nav_imp_dir tables=nav.markets network_link=udbha01_udb003_xap table_exists_action=replace
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1024 KB
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-31693: Table data object "NAV"."MARKETS" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-02063: preceding line from UDBHA01_UDB003_XAP
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "NAV"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 04:58:39
0
 
LVL 15

Accepted Solution

by:
Shaju Kumbalath earned 125 total points
ID: 24347016
Cause
To use impdp FLASHBACK_TIME or FLASHBACK_SCN the network_link must connect to the remote instance as user which has flashback query privileges granted on object to be exported/imported. Grant FLASHBACK and SELECT privileges on specific objects to be accessed during export/import or grant the FLASHBACK ANY TABLE privilege to allow export/import on all tables.
 

Granting the network_link user EXP_FULL_DATABASE and/or IMP_FULL_DATABASE role is not sufficient to access other schema objects when using FLASHBACK_TIME or FLASHBACK_SCN via impdp network_link imports.
Solution
Either configure the network_link to connect to remote site as SYSTEM schema which has FLASHBACK ANY TABLE privilege or grant FLASHBACK ANY TABLE privilege to the user specified in network_link configuration.
On import instance:
create [public] database link <link_name> connect to <user on export instance> identified by <password> using '<tnsnames connect string>';
 
refer metalink note 436106.1
0
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 

Author Comment

by:sushilt
ID: 24347030
FLASHBACK ANY TABLE privilege to allow export/import on all tables.
should i say this in the target database  or in the  database where i am running import
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 24347044
The user which network link connects (remote user) should have FLASHBACK ANY TABLE privillege
0
 

Author Comment

by:sushilt
ID: 24347076
I granted FLaskback any table to remote user,still getting the above mentioned same error
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 24351296
have u granted the privillege to the user which is mention in dblink?
0

Featured Post

Myth Busting: MongoDB Scalability (it scales!)

I was talking with one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. He mentioned to me that several customers have been telling him that “MongoDB doesn’t scale!” MongoDB’s scalability was in question?

My response was, “Is that a joke?"

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question