ORA-29913: error in executing ODCIEXTTABLEOPEN callout

i’m doing refresh of some table from our prod.
during export i did not specify the parallel option but when i do the import i specify parallel option.
and got this error ORA-29913: error in executing ODCIEXTTABLEOPEN callout

Datapump exort.

Export: Release 11.1.0.7.0 – 64bit Production on Thursday, 21 July, 2011 11:12:58

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TABLE_02″:  system/******** tables=ARADMIN.T33,ARADMIN.SERVGRP_USERLIC,ARADMIN.SERVGRP_APPLIC directory=EXPDIR logfile=t33_SERVGRP_USERLIC_SERVGRP_APPLIC dumpfile=t33_SERVGRP_USERLIC_SERVGRP_APPLIC
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12.10 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
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
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “ARADMIN”.”T33″                             10.29 GB 39592574 rows
. . exported “ARADMIN”.”SERVGRP_USERLIC”                 54.20 KB    1658 rows
. . exported “ARADMIN”.”SERVGRP_APPLIC”                  34.16 KB     620 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_02″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_02 is:
  /db/db05/oradata/PD01RMDY/exp/t33_SERVGRP_USERLIC_SERVGRP_APPLIC.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_02″ successfully completed at 11:16:58

datapump import with parallel option.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYS”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_01″:  “/******** AS SYSDBA” directory=impdpdir dumpfile=CRQ000020075375_tableresync_20110725.dmp remap_schema=aradmin:aradmin logfile=CRQ000020075375_tableres
ync.log parallel=5
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object “ARADMIN”.”T33″ failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
. . imported “ARADMIN”.”T2859″                           30.02 KB     130 rows
. . imported “ARADMIN”.”SERVGRP_USERLIC”                 49.08 KB    1477 rows
. . imported “ARADMIN”.”SERVGRP_APPLIC”                  35.41 KB     641 rows
. . imported “ARADMIN”.”T29″                             23.59 MB  108518 rows
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
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYS”.”SYS_IMPORT_FULL_01″ completed with 1 error(s) at 00:36:08

after removing the parallel option the import went fine.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYS”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_01″:  “/******** AS SYSDBA” directory=impdpdir dumpfile=CRQ000020075375_tableresync_20110725.dmp remap_schema=aradmin:aradmin logfile=xxxxx.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “ARADMIN”.”T33″                             10.57 GB 40683739 rows
. . imported “ARADMIN”.”T29″                             23.59 MB  108518 rows
. . imported “ARADMIN”.”T2859″                           30.02 KB     130 rows
. . imported “ARADMIN”.”SERVGRP_USERLIC”                 49.08 KB    1477 rows
. . imported “ARADMIN”.”SERVGRP_APPLIC”                  35.41 KB     641 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
.
.
.
thanks

bai

Advertisements

stop/kill datapump jobs the right way.

encounter this while working on datapump import and need to stop it. tried killing the process but it keeps coming.

below is the result after doing some google and check oracle metalink.

1. get the list of datapump jobs.

SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE ‘BIN$%’
ORDER BY 1,2;

OWNER_NAME JOB_NAME             OPERATION   JOB_MODE    STATE       ATTACHED_SESSIONS
———- ——————– ———– ———– ———– —————–
SYS        SYS_IMPORT_FULL_01   IMPORT      FULL        EXECUTING                   0

2. get the owner_name and job_name above and perform the kill.

SET serveroutput on
SET lines 100
DECLARE
h1 NUMBER;
BEGIN
— Format: DBMS_DATAPUMP.ATTACH(‘[job_name]’,'[owner_name]’);
h1 := DBMS_DATAPUMP.ATTACH(‘SYS_IMPORT_FULL_01‘,’SYS‘);
DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/

PL/SQL procedure successfully completed.

3. check if the job still exist.

SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE ‘BIN$%’
ORDER BY 1,2;

no rows selected