Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

impdp ora-1031

Posted on 2009-05-09
8
Medium Priority
?
1,937 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
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 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
Let’s face it: one of the reasons your organization chose a SaaS solution (whether Microsoft Dynamics 365, Netsuite or SAP) is that it is subscription-based. The upkeep is done. Or so you think.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

916 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