Home » RDBMS Server » Server Utilities » Error while import ( impdp ) (Oracle 11.2.0.1.0 linux 2.6)
Error while import ( impdp ) [message #530556] Wed, 09 November 2011 02:08
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Dear Sir/Madam,
I need to export one table and import to diff database. in both databases, same schema alrady available and table also there. in target db, already the table truncated, and we need to import only the row data from dump file.

insource db, table row count is 14100578, and same rows are imported in target table. but i am getting below error:

> impdp directory=DIR2 dumpfile=TODS_export.dmp logfile=TODS_import.log tables=NOVAR.CFC_RR_SYSTEM_EVALUATION CONTENT=DATA_ONLY

Import: Release 11.2.0.1.0 - Production on Wed Nov 9 02:14:24 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  sys/******** AS SYSDBA directory=DIR2 dumpfile=TODS_export.dmp logfile=TODS_import.log tables=NOVAR.CFC_RR_SYSTEM_EVALUATION CONTENT=DATA_ONLY
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P4481"  114.0 MB  760565 rows
. . imported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"CRSE_201001"      0 KB       0 rows
. . imported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P4721"  396.6 MB 2709669 rows
. . imported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P5797"  359.0 MB 2381870 rows
. . imported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P5857"  160.2 MB 1066783 rows
. . imported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P10494"  166.8 MB 1107934 rows
. . imported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P10109"  150.3 MB  996268 rows
. . imported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P9890"  187.1 MB 1260920 rows
. . imported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P12607"  181.3 MB 1220838 rows
. . imported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P16438"  196.7 MB 1366784 rows
. . imported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P17777"  199.4 MB 1228947 rows

UDI-31626: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3265
ORA-39076: cannot delete job SYS_IMPORT_TABLE_01 for user SYS
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1


i have performed both expdp and impdp operation as SYS user as i don't have SYSTEM password. and corresponding scehma password

source & target schema: NOVAR
soruce db: TODS
target db : DODS.
table : CFC_RR_SYSTEM_EVALUATION ( partitioned table )

these are our tesding and development database only.


fyi, pasting export log for reference:

> expdp directory=DIR2 dumpfile=TODS_export.dmp logfile=TODS_export.log tables=NOVAR.CFC_RR_SYSTEM_EVALUATION statistics=none

Export: Release 11.2.0.1.0 - Production on Wed Nov 9 01:43:44 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "statistics=none" Location: Command Line, ignored.
Legacy Mode has set reuse_dumpfiles=true parameter.
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_01":  sys/******** AS SYSDBA directory=DIR2 dumpfile=TODS_export.dmp logfile=TODS_export.log tables=NOVAR.CFC_RR_SYSTEM_EVALUATION 

reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 383.5 MB
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/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P10494"  166.8 MB 1107934 rows
. . exported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P5797"  359.0 MB 2381870 rows
. . exported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P17777"  199.4 MB 1228947 rows
. . exported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P9890"  187.1 MB 1260920 rows
. . exported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P12607"  181.3 MB 1220838 rows
. . exported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P10109"  150.3 MB  996268 rows
. . exported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P16438"  196.7 MB 1366784 rows
. . exported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P5857"  160.2 MB 1066783 rows
. . exported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P4721"  396.6 MB 2709669 rows
. . exported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"SYS_P4481"  114.0 MB  760565 rows
. . exported "NOVAR"."CFC_RR_SYSTEM_EVALUATION":"CRSE_201001"      0 KB       0 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /oracle_scratch/backup_CDM_CHSI_25oct_kesavan/TODS_export.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 01:56:05


in both databases, the directory DIR2 created and granted necessary write & read privileges to public.

After gettign this error, i just checked the row count in target table. it is getting matched with source table count.

i request you to advise me on this.

Thank you
kesavan
Previous Topic: sqlload - 2nd file with updating information
Next Topic: Can i use SQLLDR in Procedure..??
Goto Forum:
  


Current Time: Thu Mar 28 08:49:59 CDT 2024