Solved

expdp/impdp problem

Posted on 2012-04-05
4
3,207 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
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
praveencpk 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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 shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now