Solved

impdp ora-1031

Posted on 2009-05-09
8
1,818 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Note: You must have administrative privileges in order to configure lead or case queues. Salesforce.com (http://www.Salesforce.com) is a cloud-based customer relationship management (CRM) system. It is widely used around the world by sales and ma…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

734 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