Home » RDBMS Server » Server Utilities » IMPDP- ORA-31693: Table data object failed to load/unload and is being skipped (11g, 11.2.0.1, windows server 2008)
IMPDP- ORA-31693: Table data object failed to load/unload and is being skipped [message #651392] |
Tue, 17 May 2016 04:36 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ashishkumarmahanta80
Messages: 231 Registered: October 2006 Location: kolkatta
|
Senior Member |
|
|
Dear All,
While importing data_only getting below error for few tables. Below is the error-
mport: Release 11.2.0.1.0 - Production on Tue May 17 12:29:37 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_05" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_05": system/******** dumpfile=EXPDP_TESTREG_39_114_170516.DMP logfile=impdp_EXPDP_TESTREG_39_114_170516.log directory=dp_dir content=DATA_ONLY schemas=ACC,ACCEXT,ANALYSTADMIN,BA,BATJOB,BI,CLM,CMS,CNFG,CONFREP,CONFSYS,DMS,DW,GEN,HLTHCHK,INFS,INS,INTADM,MFL,MIISMA,MULTCURR,RATEENG,REPRES,RNL,SUMMUSER,TAC,TACREP,UW
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "CONFSYS"."CNFGTR_INCR_DUMP_LOG_DTLS" 339.4 MB 2238171 rows
. . imported "CONFSYS"."DML_AUDIT_LOG_FOR_DESIGNER" 43.46 KB 167 rows
. . imported "INS"."SERVICE_TRANSACTION_LOG" 20.02 MB 105990 rows
. . imported "INFS"."TM_DO_TRIAL_BAL_IMPORT" 33.49 KB 201 rows
. . imported "INS"."VEHICLEPRICEMASTER_1309" 198.0 MB 2713687 rows
. . imported "INS"."RISK_HEADERS" 1.717 MB 2385 rows
. . imported "INS"."COVERS_DETAILS" 3.547 MB 17764 rows
. . imported "ACCEXT"."ACCEXT_GENERAL_LEDGER_DETAILS" 130.1 MB 628645 rows
ORA-31693: Table data object "INS"."ACC_GENERAL_LEDGER" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-20228: Accounting Date Validation.Error in validation of A/C date..ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "INS.POPULATE_BALANCE_GL", line 36
ORA-04088: error during execution of trigger 'INS.POPULATE_BALANCE_GL'
. . imported "INS"."GENMST_LOCATION" 84.56 KB 528 rows
. . imported "INS"."GENMST_CUSTOMER" 194.3 KB 736 rows
. . imported "INS"."GENMST_CUSTOMER_HISTORY" 431.8 KB 1430 rows
Kindly suggest, how to overcome from this error?
Regards
Ashish Kumar MAhanta
|
|
|
Re: IMPDP- ORA-31693: Table data object failed to load/unload and is being skipped [message #651393 is a reply to message #651392] |
Tue, 17 May 2016 05:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ashishkumarmahanta80
Messages: 231 Registered: October 2006 Location: kolkatta
|
Senior Member |
|
|
Table structure at both the databases are same -
target database ACC_GENERAL_LEDGER source database ACC_GENERAL_LEDGER
NUM_OFFICE_CD NOT NULL NUMBER(8) NUM_OFFICE_CD NOT NULL NUMBER(8)
TXT_LEDGER_ACCOUNT_CD NOT NULL VARCHAR2(16) TXT_LEDGER_ACCOUNT_CD NOT NULL VARCHAR2(16)
NUM_FIN_YEAR NOT NULL NUMBER(4) NUM_FIN_YEAR NOT NULL NUMBER(4)
NUM_FIN_PERIOD NOT NULL NUMBER(2) NUM_FIN_PERIOD NOT NULL NUMBER(2)
NUM_REPORTING_YEAR NOT NULL NUMBER(4) NUM_REPORTING_YEAR NOT NULL NUMBER(4)
NUM_REPORTING_PERIOD NOT NULL NUMBER(2) NUM_REPORTING_PERIOD NOT NULL NUMBER(2)
NUM_TAX_YEAR NOT NULL NUMBER(4) NUM_TAX_YEAR NOT NULL NUMBER(4)
NUM_TAX_PERIOD NOT NULL NUMBER(2) NUM_TAX_PERIOD NOT NULL NUMBER(2)
DAT_VOUCHER_DATE NOT NULL DATE DAT_VOUCHER_DATE NOT NULL DATE
DAT_TRANSACTION_DATE NOT NULL DATE DAT_TRANSACTION_DATE NOT NULL DATE
DAT_POSTING_DATE NOT NULL DATE DAT_POSTING_DATE NOT NULL DATE
TXT_TRANSACTION_CATEGORY_CD NOT NULL VARCHAR2(5) TXT_TRANSACTION_CATEGORY_CD NOT NULL VARCHAR2(5)
NUM_TRANSACTION_CONTROL_NO NOT NULL NUMBER(20) NUM_TRANSACTION_CONTROL_NO NOT NULL NUMBER(20)
TXT_TRANSACTION_TYPE_CD NOT NULL VARCHAR2(5) TXT_TRANSACTION_TYPE_CD NOT NULL VARCHAR2(5)
NUM_TRANSACTION_DOCUMENT_NO NOT NULL NUMBER(10) NUM_TRANSACTION_DOCUMENT_NO NOT NULL NUMBER(10)
NUM_LINE_NO NOT NULL NUMBER(10) NUM_LINE_NO NOT NULL NUMBER(10)
TXT_EXPENSE_TYPE_CD VARCHAR2(6) TXT_EXPENSE_TYPE_CD VARCHAR2(6)
NUM_DEPARTMENT_CD NUMBER(3) NUM_DEPARTMENT_CD NUMBER(3)
NUM_REFERENCE_NO NUMBER(15) NUM_REFERENCE_NO NUMBER(15)
DAT_REFERENCE_DATE DATE DAT_REFERENCE_DATE DATE
NUM_POLICY_NO NUMBER(10) NUM_POLICY_NO NUMBER(10)
NUM_ENDORSEMENT_NO NUMBER(10) NUM_ENDORSEMENT_NO NUMBER(10)
NUM_CLAIM_NO NUMBER(20) NUM_CLAIM_NO NUMBER(20)
NUM_RI_TREATY_NO NUMBER(15) NUM_RI_TREATY_NO NUMBER(15)
TXT_DR_CR NOT NULL VARCHAR2(2) TXT_DR_CR NOT NULL VARCHAR2(2)
NUM_AMOUNT NOT NULL NUMBER(15,2) NUM_AMOUNT NOT NULL NUMBER(15,2)
TXT_DIMENSION_1_VALUE_CD VARCHAR2(20) TXT_DIMENSION_1_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_2_VALUE_CD VARCHAR2(20) TXT_DIMENSION_2_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_3_VALUE_CD VARCHAR2(20) TXT_DIMENSION_3_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_4_VALUE_CD VARCHAR2(20) TXT_DIMENSION_4_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_5_VALUE_CD VARCHAR2(20) TXT_DIMENSION_5_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_6_VALUE_CD VARCHAR2(20) TXT_DIMENSION_6_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_7_VALUE_CD VARCHAR2(20) TXT_DIMENSION_7_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_8_VALUE_CD VARCHAR2(20) TXT_DIMENSION_8_VALUE_CD VARCHAR2(20)
TXT_DIMENSION_9_VALUE_CD VARCHAR2(20) TXT_DIMENSION_9_VALUE_CD VARCHAR2(20)
TXT_REMARKS VARCHAR2(200) TXT_REMARKS VARCHAR2(200)
NUM_TRANS_ID NUMBER(15) NUM_TRANS_ID NUMBER(15)
DAT_TRANS_DATE DATE DAT_TRANS_DATE DATE
NUM_SCROLL_NO NUMBER(20) NUM_SCROLL_NO NUMBER(20)
DAT_SCROLL_DATE DATE DAT_SCROLL_DATE DATE
TXT_PAYMENT_MODE_CD VARCHAR2(2) TXT_PAYMENT_MODE_CD VARCHAR2(2)
NUM_INSTRUMENT_TYPE_CD NUMBER(2) NUM_INSTRUMENT_TYPE_CD NUMBER(2)
TXT_INSTRUMENT_NO VARCHAR2(30) TXT_INSTRUMENT_NO VARCHAR2(30)
DAT_INSTRUMENT_DATE DATE DAT_INSTRUMENT_DATE DATE
DAT_INSTRUMENT_RCVD_DATE DATE DAT_INSTRUMENT_RCVD_DATE DATE
NUM_DRAWEE_BANK_CD NUMBER(8) NUM_DRAWEE_BANK_CD NUMBER(8)
TXT_DRAWEE_BANK_BRANCH VARCHAR2(200) TXT_DRAWEE_BANK_BRANCH VARCHAR2(200)
NUM_HOUSE_BANK_BRANCH_CD NUMBER(8) NUM_HOUSE_BANK_BRANCH_CD NUMBER(8)
TXT_BANK_ACCOUNT_NO VARCHAR2(80) TXT_BANK_ACCOUNT_NO VARCHAR2(80)
NUM_PAYIN_SLIP_NO NUMBER(20) NUM_PAYIN_SLIP_NO NUMBER(20)
DAT_PAYIN_SLIP_DATE DATE DAT_PAYIN_SLIP_DATE DATE
NUM_GL_EXTRACTION_NO NUMBER(10) NUM_GL_EXTRACTION_NO NUMBER(10)
TXT_STATUS VARCHAR2(50) TXT_STATUS VARCHAR2(50)
NUM_INDEX_NO NUMBER(10) NUM_INDEX_NO NUMBER(10)
NUM_COLLECTION_OFFICE_CD NUMBER(8) NUM_COLLECTION_OFFICE_CD NUMBER(8)
NUM_INSERT_TRANS_ID NUMBER(15) NUM_INSERT_TRANS_ID NUMBER(15)
NUM_MODIFY_TRANS_ID NUMBER(15) NUM_MODIFY_TRANS_ID NUMBER(15)
DAT_INSERT_DATE DATE DAT_INSERT_DATE DATE
DAT_MODIFY_DATE DATE DAT_MODIFY_DATE DATE
NUM_SL_TYPE_CODE NUMBER(5) NUM_SL_TYPE_CODE NUMBER(5)
|
|
|
|
|
|
|
Re: IMPDP- ORA-31693: Table data object failed to load/unload and is being skipped [message #651439 is a reply to message #651398] |
Tue, 17 May 2016 23:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ashishkumarmahanta80
Messages: 231 Registered: October 2006 Location: kolkatta
|
Senior Member |
|
|
Dear Michel,
It's not a external table (source table).
Below is the script -
create table ACC_GENERAL_LEDGER
(
NUM_OFFICE_CD NUMBER(8) not null,
TXT_LEDGER_ACCOUNT_CD VARCHAR2(16) not null,
NUM_FIN_YEAR NUMBER(4) not null,
NUM_FIN_PERIOD NUMBER(2) not null,
NUM_REPORTING_YEAR NUMBER(4) not null,
NUM_REPORTING_PERIOD NUMBER(2) not null,
NUM_TAX_YEAR NUMBER(4) not null,
NUM_TAX_PERIOD NUMBER(2) not null,
DAT_VOUCHER_DATE DATE not null,
DAT_TRANSACTION_DATE DATE not null,
DAT_POSTING_DATE DATE not null,
TXT_TRANSACTION_CATEGORY_CD VARCHAR2(5) not null,
NUM_TRANSACTION_CONTROL_NO NUMBER(20) not null,
TXT_TRANSACTION_TYPE_CD VARCHAR2(5) not null,
NUM_TRANSACTION_DOCUMENT_NO NUMBER(10) not null,
NUM_LINE_NO NUMBER(10) not null,
TXT_EXPENSE_TYPE_CD VARCHAR2(6),
NUM_DEPARTMENT_CD NUMBER(3),
NUM_REFERENCE_NO NUMBER(15),
DAT_REFERENCE_DATE DATE,
NUM_POLICY_NO NUMBER(10),
NUM_ENDORSEMENT_NO NUMBER(10),
NUM_CLAIM_NO NUMBER(20),
NUM_RI_TREATY_NO NUMBER(15),
TXT_DR_CR VARCHAR2(2) not null,
NUM_AMOUNT NUMBER(15,2) not null,
TXT_DIMENSION_1_VALUE_CD VARCHAR2(20),
TXT_DIMENSION_2_VALUE_CD VARCHAR2(20),
TXT_DIMENSION_3_VALUE_CD VARCHAR2(20),
TXT_DIMENSION_4_VALUE_CD VARCHAR2(20),
TXT_DIMENSION_5_VALUE_CD VARCHAR2(20),
TXT_DIMENSION_6_VALUE_CD VARCHAR2(20),
TXT_DIMENSION_7_VALUE_CD VARCHAR2(20),
TXT_DIMENSION_8_VALUE_CD VARCHAR2(20),
TXT_DIMENSION_9_VALUE_CD VARCHAR2(20),
TXT_REMARKS VARCHAR2(200),
NUM_TRANS_ID NUMBER(15),
DAT_TRANS_DATE DATE,
NUM_SCROLL_NO NUMBER(20),
DAT_SCROLL_DATE DATE,
TXT_PAYMENT_MODE_CD VARCHAR2(2),
NUM_INSTRUMENT_TYPE_CD NUMBER(2),
TXT_INSTRUMENT_NO VARCHAR2(30),
DAT_INSTRUMENT_DATE DATE,
DAT_INSTRUMENT_RCVD_DATE DATE,
NUM_DRAWEE_BANK_CD NUMBER(8),
TXT_DRAWEE_BANK_BRANCH VARCHAR2(200),
NUM_HOUSE_BANK_BRANCH_CD NUMBER(8),
TXT_BANK_ACCOUNT_NO VARCHAR2(80),
NUM_PAYIN_SLIP_NO NUMBER(20),
DAT_PAYIN_SLIP_DATE DATE,
NUM_GL_EXTRACTION_NO NUMBER(10),
TXT_STATUS VARCHAR2(50),
NUM_INDEX_NO NUMBER(10),
NUM_COLLECTION_OFFICE_CD NUMBER(8),
NUM_INSERT_TRANS_ID NUMBER(15),
NUM_MODIFY_TRANS_ID NUMBER(15),
DAT_INSERT_DATE DATE,
DAT_MODIFY_DATE DATE,
NUM_SL_TYPE_CODE NUMBER(5)
)
tablespace INS
pctfree 10
initrans 30
maxtrans 255
storage
(
initial 152M
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column ACC_GENERAL_LEDGER.NUM_SCROLL_NO
is 'Used to store the Unique Identification Number entered during Payment mode Credit/Debit Card';
-- Create/Recreate primary, unique and foreign key constraints
alter table ACC_GENERAL_LEDGER
add constraint PK_ACC_GENERAL_LEDGER primary key (NUM_TRANSACTION_CONTROL_NO, NUM_LINE_NO)
using index
tablespace INDX
pctfree 10
initrans 60
maxtrans 255
storage
(
initial 31M
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate check constraints
alter table ACC_GENERAL_LEDGER
add check (Txt_Dr_Cr in ( 'DR', 'CR' ));
alter table ACC_GENERAL_LEDGER
add check (Num_Amount NOT in ( 0 ));
alter table ACC_GENERAL_LEDGER
add check (Txt_Dr_Cr in ( 'DR', 'CR' ));
alter table ACC_GENERAL_LEDGER
add check (Num_Amount NOT in ( 0 ));
alter table ACC_GENERAL_LEDGER
add check (Txt_Dr_Cr in ( 'DR', 'CR' ));
alter table ACC_GENERAL_LEDGER
add check (Num_Amount NOT in ( 0 ));
alter table ACC_GENERAL_LEDGER
add check (Txt_Dr_Cr in ( 'DR', 'CR' ));
alter table ACC_GENERAL_LEDGER
add check (Num_Amount NOT in ( 0 ));
alter table ACC_GENERAL_LEDGER
add check (Num_Amount NOT in ( 0 ));
alter table ACC_GENERAL_LEDGER
add check (Txt_Dr_Cr in ( 'DR', 'CR' ));
alter table ACC_GENERAL_LEDGER
add check (Num_Amount NOT in ( 0 ));
alter table ACC_GENERAL_LEDGER
add check (Txt_Dr_Cr in ( 'DR', 'CR' ));
alter table ACC_GENERAL_LEDGER
add check (Num_Amount NOT in ( 0 ));
alter table ACC_GENERAL_LEDGER
add check (Txt_Dr_Cr in ( 'DR', 'CR' ));
alter table ACC_GENERAL_LEDGER
add check (Num_Amount NOT in ( 0 ));
alter table ACC_GENERAL_LEDGER
add check (Txt_Dr_Cr in ( 'DR', 'CR' ));
-- Create/Recreate indexes
create index IDX_ACC_GENERAL_LEDGER_2 on ACC_GENERAL_LEDGER (TRUNC(DAT_VOUCHER_DATE), NUM_OFFICE_CD, TXT_LEDGER_ACCOUNT_CD)
tablespace INDX
pctfree 10
initrans 60
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index IDX_ACC_GENERAL_LEDGER_3 on ACC_GENERAL_LEDGER (DAT_TRANSACTION_DATE, NUM_OFFICE_CD)
tablespace INDX
pctfree 10
initrans 60
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index INDX_DW_1_ACC_LEDGER on ACC_GENERAL_LEDGER (NUM_REFERENCE_NO, NUM_POLICY_NO)
tablespace INDX
pctfree 10
initrans 60
maxtrans 255
storage
(
initial 22M
next 1M
minextents 1
maxextents unlimited
);
create index DW_REP.INDX_IRDA on ACC_GENERAL_LEDGER (DAT_VOUCHER_DATE, NUM_CLAIM_NO, NUM_TRANSACTION_CONTROL_NO)
tablespace INDX
pctfree 10
initrans 60
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index DW_REP.INDX_TB on ACC_GENERAL_LEDGER (TRUNC(DAT_VOUCHER_DATE))
tablespace INDX
pctfree 10
initrans 60
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Grant/Revoke object privileges
grant select on ACC_GENERAL_LEDGER to BI;
grant insert, update, delete on ACC_GENERAL_LEDGER to CNFG;
grant select, insert, update, delete, references, alter, index on ACC_GENERAL_LEDGER to PUBLIC;
I am surprised to know about it. I checked the structure at both the end and found no external tables. Request you to assist me to know, why this error msg is appearing while doing import?
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 23:37:22 CDT 2024
|