[Oracle] IMPDP 시 ORA-29913

Table 단위로 이관 요청이 있어서 쉽게 진행을 하였다.

PUMP 를 애용하는 편이라..(Enterprise 가 아니면 pump를 이용 못하거나, 주로 export를 많이 이용하라는데..쩝..)

expdp system/oracle directory=dp dumpfile=user_151120.dmp logfile=user_log.log tables=user.table1, user.tabl2....

이렇게 진행 하였다.

ftp 로 파일을 옮긴 후 IMPDP 진행

요청 받은 table 들은 truncate 로 삭제 진행

impdp system/oracle directory=dp dumpfile=user_151120.dmp logfile=user_log_imp.log content=data_only

어엇!!!에러가 발생했다.

ORA-31693: Table data object "유저명"."테이블명" failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-29400: data cartridge error

ORA-39786: Number of columns does not match between export and import database

이유

The errors occur when the ACCESS_METHOD is EXTERNAL_TABLE. This method is chosen if at least one of these conditions is met:

There is an active trigger

The table is partitioned

FGAC (Fine Grained Accesses Control) in insert mode

Referential integrity constraint exists

Unique index exists

Enabled constraint is present on pre-existing table

If any of these conditions apply, then you need to do import using DIRECT_PATH access method to solve the problem.

흠..뭐가 테이블끼리 맞지 않는 부분이 있는 듯 하다

해결방안

Follow either of the below workarounds to solve the issue:

o  Either drop the constraints/unique indexes on table before impdp is started and add access_method=direct_path to command line

- OR -

o  Rename the table before impdp, do the import and transfer the data per INSERT/SELECT to renamed table. Finally drop the imported table and rename table to old name. 

In 10g, DataPump chooses external tables for the access method to import data into a table with an unique index. On the other hand, in 11g the DataPump chooses the direct path access method even in this case. 

This issue specified in the Bug 4383811 Which has been closed as is not feasible to fix in 10g.

The ORA-39786 error is introduced (in 10.2.0.3.0) to avoid an ORA-600 error when importing data into the pre-existing table using the external tables access method.

결국은 기존 table Drop 후에 진행 하였으며, 정상적으로 해결 하였다.

기존 Table에 Trigger 가 있었으며, constraint 와 unique 를 삭제하고 재 진행할꺼면, 해당 테이블 통으로 Drop 하는 것이 편할 듯 싶었다.

버그인 부분도 있는 듯 하다.