• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2005
  • Last Modified:

impdp ora-1031

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
sushilt
Asked:
sushilt
  • 4
  • 3
1 Solution
 
Shaju KumbalathDeputy General Manager - ITCommented:
Can u post the import log file?
0
 
sushiltAuthor Commented:
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
 
Shaju KumbalathDeputy General Manager - ITCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sushiltAuthor Commented:
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
 
Shaju KumbalathDeputy General Manager - ITCommented:
The user which network link connects (remote user) should have FLASHBACK ANY TABLE privillege
0
 
sushiltAuthor Commented:
I granted FLaskback any table to remote user,still getting the above mentioned same error
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
have u granted the privillege to the user which is mention in dblink?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now