오라클 데이타베이스 에서 impdp 시 발생한 에러이다.

OS : HP-UX B.11.23 U ia64

Oracle : 10.2.0.5.0


에러 메세지>

....

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

ORA-31693: Table data object "BAK"."TB_PLAN_1010" failed to load/unload and is being skipped due to error:

ORA-31603: object "TB_PLAN_1010" of type TABLE not found in schema "BAK"

Job "SYSTEM"."STARLITS_IMPDP_JOBS" completed with 1 error(s) at 17:37:34

....


해결책 : 복구할 테이블의 메타정보를 만들어 둔 상태에서 복구한다.

SQL> create table tb_plan_1010 as select * from tb_plan where 1=0;

$ impdp system/****** dumpfile=expdp_tables_1010.dmp directory=dpdata content=data_only job_name=starlits_job logfile=starlits_impdp_jobs.log tables=bak.tb_plan_1010



metalink에서 퍼온 내용이다.>

Impdp (Datapump Import) Fails with Following Errors ORA-31693 ORA-31603 [ID 752351.1]

 수정 날짜 16-MAR-2009     유형 PROBLEM     상태 REVIEWED 

In this Document
  Symptoms
  Cause
  Solution


Applies to:

Oracle Server Enterprise Edition - Version: 10.1.0.2 to 10.2.0.4
This problem can occur on any platform.
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.4

Symptoms

Import using datapump will fail with the following errors when the export dump does not contain information about the metadata.

Errors:  

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA 
ORA-31693: Table data object "TEST"."TBL" failed to load/unload and is being skipped due to error: 
ORA-31603: object "TBL" of type TABLE not found in schema "TEST" 
ORA-31693: Table data object "TEST"."DEPT" failed to load/unload and is being skipped due to error: 
ORA-31603: object "DEPT" of type TABLE not found in schema "TEST" 
ORA-31693: Table data object "TEST"."EMP" failed to load/unload and is being skipped due to error: 
ORA-31603: object "EMP" of type TABLE not found in schema "TEST" 
ORA-31693: Table data object "TEST"."SALGRADE" failed to load/unload and is being skipped due to error: 
ORA-31603: object "SALGRADE" of type TABLE not found in schema "TEST" 
. . imported "TEST"."BONUS" 0 KB 0 rows 
. . imported "TEST"."TAB1" 0 KB 0 rows 
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 4 error(s) at 09:28:41 

Cause

When you perform an export in datapump and specify content=data_only, the dump file will not contain the metadata information of the schema objects.

So when you perform an import using datapump into a different schema or in the same schema where the metadata does not exists then the aforementioned errors will occur.

Solution

When the error ORA-31693 ORA-31603, occurs during impdp then confirm whether the export dump was taken with contents=all option. You can verify that by using the 'sqlfile' option in the impdp process. By specifying sqlfile in the impdp, the datapump generates a text file which contains the metadata information that was loaded in the export process. If the sqlfile contains no metadata information then the dump was not exported with the option content=all option.

To avoid this error either precreate the objects before the import or export using content=all parameter.

profile