Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

impdp ora-1031

Posted on 2009-05-09
8
Medium Priority
?
1,960 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
  • 4
  • 3
7 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 500 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
Independent Software Vendors: 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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

Note: You must have administrative privileges in order to create/edit Roles. Salesforce.com (http://www.salesforce.com/) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales and marke…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month14 days, 10 hours left to enroll

577 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