Solved

expdp/impdp problem

Posted on 2012-04-05
4
4,137 Views
Last Modified: 2012-05-08
I have a table called return_sets that exists in two different schemas (xmltest and xmltestb) with the same structures. The table in schema xmltestb contains data that includes an xmldata datatype, but is not indexed. The table in schema xmltest contains no data but has an xmlindex on the xmldata column. I tried to load the data from the xmltestb table into the xmltest table using expdp and impdp, but am getting errors. The commands I've used are:
expdp xmltestb/bpword@xmldev DUMPFILE=ret_sets_data.dmp DIRECTORY=BIN_DIRECTORY TABLES=RETURN_SETS CONTENT=DATA_ONLY
impdp xmltest/pword@xmldev DUMPFILE=ret_sets_data.dmp DIRECTORY=BIN_DIRECTORY TABLES=RETURN_SETS REMAP_SCHEMA=xmltestb:xmltest
Execution of the expdp command completes successfully, but the impdp command shows the following error messages:
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning and Data Mining options
Master table "XMLTEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "XMLTEST"."SYS_IMPORT_TABLE_01":  xmltest/********@xmldev DUMPFILE=ret_sets_data.dmp DIRECTORY=BIN_DIRECTORY TABLES=RETURN_SETS REMAP_SCHEMA=xmltestb:xmltest
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "XMLTEST"."RETURN_SETS" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
ORA-00600: internal error code, arguments: [kglslod:subheap], [8], [0x3E1D712E8], [], [], [], [], [], [], [], [], []
Job "XMLTEST"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:47:03

The error message documentation below doesn't seem to provide too much help in resolving the problem.
ORA-31693: Table data object string failed to load/unload and is being skipped due to error: string Cause: Table failed to load or unload due to some error.
Action: Check load/unload error, correct problem and retry command.

ORA-29913: error in executing string callout Cause: The execution of the specified callout caused an error.
Action: Examine the error messages take appropriate action.

ORA-29875: failed in the execution of the ODCIINDEXINSERT routine Cause: Failed to successfully execute the ODCIIndexInsert routine.
Action: Check to see if the routine has been coded correctly.

I would appreciate any help in resolving this issue.
0
Comment
Question by:awking00
[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
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
Praveen Kumar Chandrashekatr earned 500 total points
ID: 37812581
you also getting and ORA-600 error which oracle internal error.

if you have oracle support then use ORA-600 lookup tool on MOS or raise an incident to the oracle support center.

Also check this note 780848.1 for that particular ORA-600 error.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37817091
--expdp xmltestb/bpword@xmldev
--impdp xmltest/pword@xmldev??

is there any need to use expdp when you are in same database server??

you can simply use as insert into schema.table select * from schema.table;

I am not sure why ORA-0600 have got triggered...? also if you have to use datapump only then add a (table_exists_action=append) and see if this resolves your problem..
0
 
LVL 1

Expert Comment

by:Faher
ID: 37819502
kindly take full export of particular table dont use content=data_only
import that table with table_exists_action=append or replace

If exported table is on different tablespace then imported schema (xmltest) then also use remap_tablespace=exported_Tablespace:imported_tablespace

let suppose if xmltestb schema has different tablespace then xmltest schema then you have to use repmap_tablespace option too. If boht schemas are using same tablespace then you dont need to define remap_tablespace
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37821345
Expert Faher(table_exists_action=append) has already been suggested, you don't have to repeat an information which has already posted..!!!
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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

717 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