Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to import a dmp file provided by our dba to my dev database. I've been able to export the contents to a SQLFILE and the contents look ok. I tried importing the schema and it kept on failing (same errors as shown below); so I tried running the SQL directly from the sqlfile - this created all the schema objects but didn't import the data.

The export was from the expenses schema from our test database. I already have another schema by that name in my dev database that I don't want to touch; so I'm remapping expenses to expenses1.

This schema (expenses1) has READ and WRITE privilege on the data_pump_dir directory. It has, as far as I can tell, all the privileges it needs (create table, view, procedure, sequence, etc) as well as IMP_FULL_DATABASE.

[oradev@DIAS45 dpdump]$ impdp expenses1/expenses1 logfile=expenses1.log
  remap_schema=EXPENSES:EXPENSES1 directory=data_pump_dir
  dumpfile=ossarct_expenses_expdp.dmp
  content=DATA_ONLY schemas=EXPENSES include=TABLE

Import: Release 11.2.0.3.0 - Production on Tue Apr 16 09:28:21 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDI-31626: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-39086: cannot retrieve job information
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

[oradev@DIAS45 dpdump]$ cat expenses1.log
;;;
Import: Release 11.2.0.3.0 - Production on Tue Apr 16 09:28:21 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "EXPENSES1"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "EXPENSES1"."SYS_IMPORT_SCHEMA_01":  expenses1/******** logfile=expenses1.log
  remap_schema=EXPENSES:EXPENSES1 directory=data_pump_dir
  dumpfile=ossarct_expenses_expdp.dmp content=DATA_ONLY schemas=EXPENSES include=TABLE
ORA-39097: Data Pump job encountered unexpected error -1031
ORA-39065: unexpected master process exception in DISPATCH
ORA-01031: insufficient privileges

I can't tell from the above what privilege is missing, or if my command is telling impdp to do more than I'm expecting it to. I've tried skipping the schemas and include parameters with no difference.

share|improve this question
1  
Have you seen the MOS note DataPump Import (IMPDP) Fails With Errors ORA-1031 UDI-31626 If DBA Role Is Missing -- ID 1459430.1‌​? Using a DBA user to perform the import (not necessarily expenses1) might solve the issue. –  Vincent Malgrat Apr 16 '13 at 10:01
    
Don't have MOS access myself as a mere contractor (might have to see if I can) - but I'll try the DBA role, thanks. –  Jeffrey Kemp Apr 16 '13 at 13:13
    
@VincentMalgrat: thanks. Tried it as SYSTEM and it worked. –  Jeffrey Kemp Apr 17 '13 at 5:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.