Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: March 27, 2017 - 10:30 am UTC

Last updated: April 07, 2017 - 2:25 am UTC

Version: 10.2.0.4.0

Viewed 1000+ times

You Asked

I am trying to import individual sub-partition to existing Partitioned table have same structure as source table. But giving me this error. 


$impdp livedb/livedb@***** dumpfile=branchoct15_%U.dmp  directory=IMP15 tables=APP_BRANCH remap_schema=induat_live:livedb remap_tablespace=branch:branch  table_exists_action=append

Import: Release 10.2.0.4.0 - 64bit Production on Monday, 27 March, 2017 12:47:30

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "LIVEDB"."SYS_IMPORT_TABLE_14" successfully loaded/unloaded
Starting "LIVEDB"."SYS_IMPORT_TABLE_14":  livedb/********@**** dumpfile=branchoct15_%U.dmp directory=IMP15 tables=APP_BRANCH remap_schema=induat_live:livedb remap_tablespace=branch:branch table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "LIVEDB"."APP_BRANCH" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "LIVEDB"."APP_BRANCH":"BRN_PART_2015"."BRN_SUBPART_OCT15" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-02373: Error parsing insert statement for table "LIVEDB"."APP_BRANCH".
ORA-00904: "FIN_DATE": invalid identifier
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "LIVEDB"."SYS_IMPORT_TABLE_14" completed with 2 error(s) at 12:47:32


DDL of source table..

CREATE TABLE INDUAT_LIVE.APP_BRANCH
(
  ID               VARCHAR2(18 BYTE),
  RCD_TYP          VARCHAR2(2 BYTE),
  TRN_TYP          VARCHAR2(4 BYTE),
  SRL_NUM          VARCHAR2(12 BYTE),
  SYS_DTE_TME      VARCHAR2(24 BYTE),
  TRN_AMT          VARCHAR2(40 BYTE),
  TRN_CRR          VARCHAR2(3 BYTE),
  CRD_NUM          VARCHAR2(128 BYTE),
  CUS_ID           VARCHAR2(9 BYTE),
  BSN_DTE          VARCHAR2(8 BYTE),
  FEE_CRR1         VARCHAR2(3 BYTE),
  FEE_AMT1         VARCHAR2(15 BYTE),
  FEE_IND1         VARCHAR2(1 BYTE),
  FEE_CRR2         VARCHAR2(3 BYTE),
  FEE_AMT2         VARCHAR2(15 BYTE),
  FEE_IND2         VARCHAR2(1 BYTE),
  FEE_CRR3         VARCHAR2(3 BYTE),
  FEE_AMT3         VARCHAR2(15 BYTE),
  FEE_IND3         VARCHAR2(1 BYTE),
  FEE_CRR4         VARCHAR2(3 BYTE),
  FEE_AMT4         VARCHAR2(15 BYTE),
  FEE_IND4         VARCHAR2(1 BYTE),
  FEE_CRR5         VARCHAR2(3 BYTE),
  FEE_AMT5         VARCHAR2(15 BYTE),
  FEE_IND5         VARCHAR2(1 BYTE),
  DBT_BNK_CDE      VARCHAR2(11 BYTE),
  DBT_CUS_ID       VARCHAR2(8 BYTE),
  DBT_ACC_NUM      VARCHAR2(24 BYTE),
  CRD_BNK_CDE      VARCHAR2(11 BYTE),
  CRD_CUS_ID       VARCHAR2(8 BYTE),
  CRD_ACC_NUM      VARCHAR2(24 BYTE),
  ORG_TRN_AMT      VARCHAR2(15 BYTE),
  ORG_TRN_TYP      VARCHAR2(4 BYTE),
  ORG_SRL_NUM      VARCHAR2(12 BYTE),
  ORG_DTE_TME      VARCHAR2(14 BYTE),
  ORG_FEE_CRR1     VARCHAR2(3 BYTE),
  ORG_FEE_AMT1     VARCHAR2(15 BYTE),
  ORG_FEE_IND1     VARCHAR2(1 BYTE),
  ORG_FEE_CRR2     VARCHAR2(3 BYTE),
  ORG_FEE_AMT2     VARCHAR2(15 BYTE),
  ORG_FEE_IND2     VARCHAR2(1 BYTE),
  ORG_FEE_CRR3     VARCHAR2(3 BYTE),
  ORG_FEE_AMT3     VARCHAR2(15 BYTE),
  ORG_FEE_IND3     VARCHAR2(1 BYTE),
  ORG_FEE_CRR4     VARCHAR2(3 BYTE),
  ORG_FEE_AMT4     VARCHAR2(15 BYTE),
  ORG_FEE_IND4     VARCHAR2(1 BYTE),
  ORG_FEE_CRR5     VARCHAR2(3 BYTE),
  ORG_FEE_AMT5     VARCHAR2(15 BYTE),
  ORG_FEE_IND5     VARCHAR2(1 BYTE),
  DVI_TYP          VARCHAR2(3 BYTE),
  DVI_ID           VARCHAR2(15 BYTE),
  PAY_CDE          VARCHAR2(3 BYTE),
  CMR_ID           VARCHAR2(28 BYTE),
  RES_RCV_FRM_HST  VARCHAR2(1 BYTE),
  FLD_XX1          VARCHAR2(40 BYTE),
  FLD_XX2          VARCHAR2(40 BYTE),
  NET_CDE          VARCHAR2(4 BYTE),
  CBS_FLG          VARCHAR2(1 BYTE),
  HSH_VAL          VARCHAR2(25 BYTE),
  BRN_CDE          VARCHAR2(4 BYTE),
  ACC_TYP          VARCHAR2(10 BYTE),
  GL_AMT           VARCHAR2(40 BYTE),
  FLAG             VARCHAR2(10 BYTE),
  PNT_RCN_ID       VARCHAR2(18 BYTE),
  ACNTID           VARCHAR2(22 BYTE),
  EXCEP            VARCHAR2(100 BYTE),
  FILENAME         VARCHAR2(25 BYTE),
  PARSED_DATE      DATE,
  TRN_YY           VARCHAR2(4 BYTE),
  TRN_MM           VARCHAR2(2 BYTE),
  SOURCE           VARCHAR2(15 BYTE),
  FIN_DATE         VARCHAR2(10 BYTE)
)
TABLESPACE BRANCH
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
NOLOGGING
PARTITION BY RANGE (TRN_YY)
SUBPARTITION BY LIST (TRN_MM)
(  
  PARTITION BRN_PART_2015 VALUES LESS THAN ('2016')
    NOLOGGING
    COMPRESS
    TABLESPACE BRANCH
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               )
  ( SUBPARTITION BRN_SUBPART_JAN15 VALUES ('01')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_FEB15 VALUES ('02')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_MAR15 VALUES ('03')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_APR15 VALUES ('04')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_MAY15 VALUES ('05')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_JUN15 VALUES ('06')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_JUL15 VALUES ('07')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_AUG15 VALUES ('08')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_SEP15 VALUES ('09')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_OCT15 VALUES ('10')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_NOV15 VALUES ('11')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_DEC15 VALUES ('12')      TABLESPACE BRANCH ),  
  PARTITION BRN_PART_2016 VALUES LESS THAN ('2017')
    NOLOGGING
    COMPRESS
    TABLESPACE BRANCH
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               )
  ( SUBPARTITION BRN_SUBPART_JAN16 VALUES ('01')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_FEB16 VALUES ('02')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_MAR16 VALUES ('03')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_APR16 VALUES ('04')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_MAY16 VALUES ('05')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_JUN16 VALUES ('06')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_JUL16 VALUES ('07')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_AUG16 VALUES ('08')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_SEP16 VALUES ('09')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_OCT16 VALUES ('10')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_NOV16 VALUES ('11')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_DEC16 VALUES ('12')      TABLESPACE BRANCH )
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



DDL of Target table...

CREATE TABLE LIVEDB.APP_BRANCH
(
  ID               VARCHAR2(18 BYTE),
  RCD_TYP          VARCHAR2(2 BYTE),
  TRN_TYP          VARCHAR2(4 BYTE),
  SRL_NUM          VARCHAR2(12 BYTE),
  SYS_DTE_TME      VARCHAR2(24 BYTE),
  TRN_AMT          VARCHAR2(40 BYTE),
  TRN_CRR          VARCHAR2(3 BYTE),
  CRD_NUM          VARCHAR2(128 BYTE),
  CUS_ID           VARCHAR2(9 BYTE),
  BSN_DTE          VARCHAR2(8 BYTE),
  FEE_CRR1         VARCHAR2(3 BYTE),
  FEE_AMT1         VARCHAR2(16 BYTE),
  FEE_IND1         VARCHAR2(1 BYTE),
  FEE_CRR2         VARCHAR2(3 BYTE),
  FEE_AMT2         VARCHAR2(16 BYTE),
  FEE_IND2         VARCHAR2(1 BYTE),
  FEE_CRR3         VARCHAR2(3 BYTE),
  FEE_AMT3         VARCHAR2(16 BYTE),
  FEE_IND3         VARCHAR2(1 BYTE),
  FEE_CRR4         VARCHAR2(3 BYTE),
  FEE_AMT4         VARCHAR2(16 BYTE),
  FEE_IND4         VARCHAR2(1 BYTE),
  FEE_CRR5         VARCHAR2(3 BYTE),
  FEE_AMT5         VARCHAR2(16 BYTE),
  FEE_IND5         VARCHAR2(1 BYTE),
  DBT_BNK_CDE      VARCHAR2(11 BYTE),
  DBT_CUS_ID       VARCHAR2(8 BYTE),
  DBT_ACC_NUM      VARCHAR2(24 BYTE),
  CRD_BNK_CDE      VARCHAR2(11 BYTE),
  CRD_CUS_ID       VARCHAR2(8 BYTE),
  CRD_ACC_NUM      VARCHAR2(24 BYTE),
  ORG_TRN_AMT      VARCHAR2(16 BYTE),
  ORG_TRN_TYP      VARCHAR2(4 BYTE),
  ORG_SRL_NUM      VARCHAR2(12 BYTE),
  ORG_DTE_TME      VARCHAR2(14 BYTE),
  ORG_FEE_CRR1     VARCHAR2(3 BYTE),
  ORG_FEE_AMT1     VARCHAR2(16 BYTE),
  ORG_FEE_IND1     VARCHAR2(1 BYTE),
  ORG_FEE_CRR2     VARCHAR2(3 BYTE),
  ORG_FEE_AMT2     VARCHAR2(16 BYTE),
  ORG_FEE_IND2     VARCHAR2(1 BYTE),
  ORG_FEE_CRR3     VARCHAR2(3 BYTE),
  ORG_FEE_AMT3     VARCHAR2(16 BYTE),
  ORG_FEE_IND3     VARCHAR2(1 BYTE),
  ORG_FEE_CRR4     VARCHAR2(3 BYTE),
  ORG_FEE_AMT4     VARCHAR2(16 BYTE),
  ORG_FEE_IND4     VARCHAR2(1 BYTE),
  ORG_FEE_CRR5     VARCHAR2(3 BYTE),
  ORG_FEE_AMT5     VARCHAR2(16 BYTE),
  ORG_FEE_IND5     VARCHAR2(1 BYTE),
  DVI_TYP          VARCHAR2(3 BYTE),
  DVI_ID           VARCHAR2(16 BYTE),
  PAY_CDE          VARCHAR2(3 BYTE),
  CMR_ID           VARCHAR2(28 BYTE),
  RES_RCV_FRM_HST  VARCHAR2(1 BYTE),
  FLD_XX1          VARCHAR2(40 BYTE),
  FLD_XX2          VARCHAR2(40 BYTE),
  NET_CDE          VARCHAR2(4 BYTE),
  CBS_FLG          VARCHAR2(1 BYTE),
  HSH_VAL          VARCHAR2(25 BYTE),
  BRN_CDE          VARCHAR2(4 BYTE),
  ACC_TYP          VARCHAR2(10 BYTE),
  GL_AMT           VARCHAR2(40 BYTE),
  FLAG             VARCHAR2(10 BYTE),
  PNT_RCN_ID       VARCHAR2(18 BYTE),
  ACNTID           VARCHAR2(22 BYTE),
  EXCEP            VARCHAR2(100 BYTE),
  FILENAME         VARCHAR2(25 BYTE),
  PARSED_DATE      DATE,
  TRN_YY           VARCHAR2(4 BYTE),
  TRN_MM           VARCHAR2(2 BYTE),
  SOURCE           VARCHAR2(15 BYTE)
)
TABLESPACE BRANCH
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
PARTITION BY RANGE (TRN_YY)
SUBPARTITION BY LIST (TRN_MM)
(  
  PARTITION BRN_PART_2015 VALUES LESS THAN ('2016')
    NOLOGGING
    NOCOMPRESS
    TABLESPACE BRANCH
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                BUFFER_POOL      DEFAULT
               )
  ( SUBPARTITION BRN_SUBPART_JAN15 VALUES ('01')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_FEB15 VALUES ('02')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_MAR15 VALUES ('03')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_APR15 VALUES ('04')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_MAY15 VALUES ('05')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_JUN15 VALUES ('06')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_JUL15 VALUES ('07')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_AUG15 VALUES ('08')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_SEP15 VALUES ('09')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_OCT15 VALUES ('10')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_NOV15 VALUES ('11')      TABLESPACE BRANCH,
    SUBPARTITION BRN_SUBPART_DEC15 VALUES ('12')      TABLESPACE BRANCH )
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


Here I have export from 11.2.0.4.0 to import in 10.2.0.4.0

and Connor said...

My suspicion is a version inconsistency. It's fine with same to same, ie


SQL> drop table scott.app_branch purge;

Table dropped.

SQL> drop table scott.APP_BRANCH1 purge;

Table dropped.

SQL>
SQL> CREATE TABLE SCOTT.APP_BRANCH
  2  (
  3    ID               VARCHAR2(18 BYTE),
  4    RCD_TYP          VARCHAR2(2 BYTE),
  5    TRN_TYP          VARCHAR2(4 BYTE),
  6    SRL_NUM          VARCHAR2(12 BYTE),
  7    SYS_DTE_TME      VARCHAR2(24 BYTE),
  8    TRN_AMT          VARCHAR2(40 BYTE),
  9    TRN_CRR          VARCHAR2(3 BYTE),
 10    CRD_NUM          VARCHAR2(128 BYTE),
 11    CUS_ID           VARCHAR2(9 BYTE),
 12    BSN_DTE          VARCHAR2(8 BYTE),
 13    FEE_CRR1         VARCHAR2(3 BYTE),
 14    FEE_AMT1         VARCHAR2(15 BYTE),
 15    FEE_IND1         VARCHAR2(1 BYTE),
 16    FEE_CRR2         VARCHAR2(3 BYTE),
 17    FEE_AMT2         VARCHAR2(15 BYTE),
 18    FEE_IND2         VARCHAR2(1 BYTE),
 19    FEE_CRR3         VARCHAR2(3 BYTE),
 20    FEE_AMT3         VARCHAR2(15 BYTE),
 21    FEE_IND3         VARCHAR2(1 BYTE),
 22    FEE_CRR4         VARCHAR2(3 BYTE),
 23    FEE_AMT4         VARCHAR2(15 BYTE),
 24    FEE_IND4         VARCHAR2(1 BYTE),
 25    FEE_CRR5         VARCHAR2(3 BYTE),
 26    FEE_AMT5         VARCHAR2(15 BYTE),
 27    FEE_IND5         VARCHAR2(1 BYTE),
 28    DBT_BNK_CDE      VARCHAR2(11 BYTE),
 29    DBT_CUS_ID       VARCHAR2(8 BYTE),
 30    DBT_ACC_NUM      VARCHAR2(24 BYTE),
 31    CRD_BNK_CDE      VARCHAR2(11 BYTE),
 32    CRD_CUS_ID       VARCHAR2(8 BYTE),
 33    CRD_ACC_NUM      VARCHAR2(24 BYTE),
 34    ORG_TRN_AMT      VARCHAR2(15 BYTE),
 35    ORG_TRN_TYP      VARCHAR2(4 BYTE),
 36    ORG_SRL_NUM      VARCHAR2(12 BYTE),
 37    ORG_DTE_TME      VARCHAR2(14 BYTE),
 38    ORG_FEE_CRR1     VARCHAR2(3 BYTE),
 39    ORG_FEE_AMT1     VARCHAR2(15 BYTE),
 40    ORG_FEE_IND1     VARCHAR2(1 BYTE),
 41    ORG_FEE_CRR2     VARCHAR2(3 BYTE),
 42    ORG_FEE_AMT2     VARCHAR2(15 BYTE),
 43    ORG_FEE_IND2     VARCHAR2(1 BYTE),
 44    ORG_FEE_CRR3     VARCHAR2(3 BYTE),
 45    ORG_FEE_AMT3     VARCHAR2(15 BYTE),
 46    ORG_FEE_IND3     VARCHAR2(1 BYTE),
 47    ORG_FEE_CRR4     VARCHAR2(3 BYTE),
 48    ORG_FEE_AMT4     VARCHAR2(15 BYTE),
 49    ORG_FEE_IND4     VARCHAR2(1 BYTE),
 50    ORG_FEE_CRR5     VARCHAR2(3 BYTE),
 51    ORG_FEE_AMT5     VARCHAR2(15 BYTE),
 52    ORG_FEE_IND5     VARCHAR2(1 BYTE),
 53    DVI_TYP          VARCHAR2(3 BYTE),
 54    DVI_ID           VARCHAR2(15 BYTE),
 55    PAY_CDE          VARCHAR2(3 BYTE),
 56    CMR_ID           VARCHAR2(28 BYTE),
 57    RES_RCV_FRM_HST  VARCHAR2(1 BYTE),
 58    FLD_XX1          VARCHAR2(40 BYTE),
 59    FLD_XX2          VARCHAR2(40 BYTE),
 60    NET_CDE          VARCHAR2(4 BYTE),
 61    CBS_FLG          VARCHAR2(1 BYTE),
 62    HSH_VAL          VARCHAR2(25 BYTE),
 63    BRN_CDE          VARCHAR2(4 BYTE),
 64    ACC_TYP          VARCHAR2(10 BYTE),
 65    GL_AMT           VARCHAR2(40 BYTE),
 66    FLAG             VARCHAR2(10 BYTE),
 67    PNT_RCN_ID       VARCHAR2(18 BYTE),
 68    ACNTID           VARCHAR2(22 BYTE),
 69    EXCEP            VARCHAR2(100 BYTE),
 70    FILENAME         VARCHAR2(25 BYTE),
 71    PARSED_DATE      DATE,
 72    TRN_YY           VARCHAR2(4 BYTE),
 73    TRN_MM           VARCHAR2(2 BYTE),
 74    SOURCE           VARCHAR2(15 BYTE),
 75    FIN_DATE         VARCHAR2(10 BYTE)
 76  )
 77  PARTITION BY RANGE (TRN_YY)
 78  SUBPARTITION BY LIST (TRN_MM)
 79  (
 80    PARTITION BRN_PART_2015 VALUES LESS THAN ('2016')
 81      NOLOGGING
 82      COMPRESS
 83      PCTFREE    10
 84      INITRANS   1
 85      MAXTRANS   255
 86      STORAGE    (
 87                  BUFFER_POOL      DEFAULT
 88                 )
 89    ( SUBPARTITION BRN_SUBPART_JAN15 VALUES ('01')      ,
 90      SUBPARTITION BRN_SUBPART_FEB15 VALUES ('02')      ,
 91      SUBPARTITION BRN_SUBPART_MAR15 VALUES ('03')      ,
 92      SUBPARTITION BRN_SUBPART_APR15 VALUES ('04')      ,
 93      SUBPARTITION BRN_SUBPART_MAY15 VALUES ('05')      ,
 94      SUBPARTITION BRN_SUBPART_JUN15 VALUES ('06')      ,
 95      SUBPARTITION BRN_SUBPART_JUL15 VALUES ('07')      ,
 96      SUBPARTITION BRN_SUBPART_AUG15 VALUES ('08')      ,
 97      SUBPARTITION BRN_SUBPART_SEP15 VALUES ('09')      ,
 98      SUBPARTITION BRN_SUBPART_OCT15 VALUES ('10')      ,
 99      SUBPARTITION BRN_SUBPART_NOV15 VALUES ('11')      ,
100      SUBPARTITION BRN_SUBPART_DEC15 VALUES ('12')       ),
101    PARTITION BRN_PART_2016 VALUES LESS THAN ('2017')
102      NOLOGGING
103      COMPRESS
104      PCTFREE    10
105      INITRANS   1
106      MAXTRANS   255
107      STORAGE    (
108                  BUFFER_POOL      DEFAULT
109                 )
110    ( SUBPARTITION BRN_SUBPART_JAN16 VALUES ('01')      ,
111      SUBPARTITION BRN_SUBPART_FEB16 VALUES ('02')      ,
112      SUBPARTITION BRN_SUBPART_MAR16 VALUES ('03')      ,
113      SUBPARTITION BRN_SUBPART_APR16 VALUES ('04')      ,
114      SUBPARTITION BRN_SUBPART_MAY16 VALUES ('05')      ,
115      SUBPARTITION BRN_SUBPART_JUN16 VALUES ('06')      ,
116      SUBPARTITION BRN_SUBPART_JUL16 VALUES ('07')      ,
117      SUBPARTITION BRN_SUBPART_AUG16 VALUES ('08')      ,
118      SUBPARTITION BRN_SUBPART_SEP16 VALUES ('09')      ,
119      SUBPARTITION BRN_SUBPART_OCT16 VALUES ('10')      ,
120      SUBPARTITION BRN_SUBPART_NOV16 VALUES ('11')      ,
121      SUBPARTITION BRN_SUBPART_DEC16 VALUES ('12')       )
122  )
123  NOCOMPRESS
124  NOCACHE
125  NOPARALLEL
126  MONITORING;

Table created.

SQL>
SQL>
SQL>
SQL> CREATE TABLE scott.APP_BRANCH1
  2  (
  3    ID               VARCHAR2(18 BYTE),
  4    RCD_TYP          VARCHAR2(2 BYTE),
  5    TRN_TYP          VARCHAR2(4 BYTE),
  6    SRL_NUM          VARCHAR2(12 BYTE),
  7    SYS_DTE_TME      VARCHAR2(24 BYTE),
  8    TRN_AMT          VARCHAR2(40 BYTE),
  9    TRN_CRR          VARCHAR2(3 BYTE),
 10    CRD_NUM          VARCHAR2(128 BYTE),
 11    CUS_ID           VARCHAR2(9 BYTE),
 12    BSN_DTE          VARCHAR2(8 BYTE),
 13    FEE_CRR1         VARCHAR2(3 BYTE),
 14    FEE_AMT1         VARCHAR2(16 BYTE),
 15    FEE_IND1         VARCHAR2(1 BYTE),
 16    FEE_CRR2         VARCHAR2(3 BYTE),
 17    FEE_AMT2         VARCHAR2(16 BYTE),
 18    FEE_IND2         VARCHAR2(1 BYTE),
 19    FEE_CRR3         VARCHAR2(3 BYTE),
 20    FEE_AMT3         VARCHAR2(16 BYTE),
 21    FEE_IND3         VARCHAR2(1 BYTE),
 22    FEE_CRR4         VARCHAR2(3 BYTE),
 23    FEE_AMT4         VARCHAR2(16 BYTE),
 24    FEE_IND4         VARCHAR2(1 BYTE),
 25    FEE_CRR5         VARCHAR2(3 BYTE),
 26    FEE_AMT5         VARCHAR2(16 BYTE),
 27    FEE_IND5         VARCHAR2(1 BYTE),
 28    DBT_BNK_CDE      VARCHAR2(11 BYTE),
 29    DBT_CUS_ID       VARCHAR2(8 BYTE),
 30    DBT_ACC_NUM      VARCHAR2(24 BYTE),
 31    CRD_BNK_CDE      VARCHAR2(11 BYTE),
 32    CRD_CUS_ID       VARCHAR2(8 BYTE),
 33    CRD_ACC_NUM      VARCHAR2(24 BYTE),
 34    ORG_TRN_AMT      VARCHAR2(16 BYTE),
 35    ORG_TRN_TYP      VARCHAR2(4 BYTE),
 36    ORG_SRL_NUM      VARCHAR2(12 BYTE),
 37    ORG_DTE_TME      VARCHAR2(14 BYTE),
 38    ORG_FEE_CRR1     VARCHAR2(3 BYTE),
 39    ORG_FEE_AMT1     VARCHAR2(16 BYTE),
 40    ORG_FEE_IND1     VARCHAR2(1 BYTE),
 41    ORG_FEE_CRR2     VARCHAR2(3 BYTE),
 42    ORG_FEE_AMT2     VARCHAR2(16 BYTE),
 43    ORG_FEE_IND2     VARCHAR2(1 BYTE),
 44    ORG_FEE_CRR3     VARCHAR2(3 BYTE),
 45    ORG_FEE_AMT3     VARCHAR2(16 BYTE),
 46    ORG_FEE_IND3     VARCHAR2(1 BYTE),
 47    ORG_FEE_CRR4     VARCHAR2(3 BYTE),
 48    ORG_FEE_AMT4     VARCHAR2(16 BYTE),
 49    ORG_FEE_IND4     VARCHAR2(1 BYTE),
 50    ORG_FEE_CRR5     VARCHAR2(3 BYTE),
 51    ORG_FEE_AMT5     VARCHAR2(16 BYTE),
 52    ORG_FEE_IND5     VARCHAR2(1 BYTE),
 53    DVI_TYP          VARCHAR2(3 BYTE),
 54    DVI_ID           VARCHAR2(16 BYTE),
 55    PAY_CDE          VARCHAR2(3 BYTE),
 56    CMR_ID           VARCHAR2(28 BYTE),
 57    RES_RCV_FRM_HST  VARCHAR2(1 BYTE),
 58    FLD_XX1          VARCHAR2(40 BYTE),
 59    FLD_XX2          VARCHAR2(40 BYTE),
 60    NET_CDE          VARCHAR2(4 BYTE),
 61    CBS_FLG          VARCHAR2(1 BYTE),
 62    HSH_VAL          VARCHAR2(25 BYTE),
 63    BRN_CDE          VARCHAR2(4 BYTE),
 64    ACC_TYP          VARCHAR2(10 BYTE),
 65    GL_AMT           VARCHAR2(40 BYTE),
 66    FLAG             VARCHAR2(10 BYTE),
 67    PNT_RCN_ID       VARCHAR2(18 BYTE),
 68    ACNTID           VARCHAR2(22 BYTE),
 69    EXCEP            VARCHAR2(100 BYTE),
 70    FILENAME         VARCHAR2(25 BYTE),
 71    PARSED_DATE      DATE,
 72    TRN_YY           VARCHAR2(4 BYTE),
 73    TRN_MM           VARCHAR2(2 BYTE),
 74    SOURCE           VARCHAR2(15 BYTE)
 75  )
 76  PCTUSED    0
 77  PCTFREE    10
 78  INITRANS   1
 79  MAXTRANS   255
 80  PARTITION BY RANGE (TRN_YY)
 81  SUBPARTITION BY LIST (TRN_MM)
 82  (
 83    PARTITION BRN_PART_2015 VALUES LESS THAN ('2016')
 84      NOLOGGING
 85      NOCOMPRESS
 86      PCTFREE    10
 87      INITRANS   1
 88      MAXTRANS   255
 89      STORAGE    (
 90                  BUFFER_POOL      DEFAULT
 91                 )
 92    ( SUBPARTITION BRN_SUBPART_JAN15 VALUES ('01')      ,
 93      SUBPARTITION BRN_SUBPART_FEB15 VALUES ('02')      ,
 94      SUBPARTITION BRN_SUBPART_MAR15 VALUES ('03')      ,
 95      SUBPARTITION BRN_SUBPART_APR15 VALUES ('04')      ,
 96      SUBPARTITION BRN_SUBPART_MAY15 VALUES ('05')      ,
 97      SUBPARTITION BRN_SUBPART_JUN15 VALUES ('06')      ,
 98      SUBPARTITION BRN_SUBPART_JUL15 VALUES ('07')      ,
 99      SUBPARTITION BRN_SUBPART_AUG15 VALUES ('08')      ,
100      SUBPARTITION BRN_SUBPART_SEP15 VALUES ('09')      ,
101      SUBPARTITION BRN_SUBPART_OCT15 VALUES ('10')      ,
102      SUBPARTITION BRN_SUBPART_NOV15 VALUES ('11')      ,
103      SUBPARTITION BRN_SUBPART_DEC15 VALUES ('12')       )
104  )
105  NOCOMPRESS
106  NOCACHE
107  NOPARALLEL
108  MONITORING;

Table created.

SQL>
SQL>
SQL> insert into scott.app_branch
  2  select
  3  rownum,
  4  'X',
  5  'X',
  6  rownum,
  7  rownum,
  8  rownum,
  9  'X',
 10  rownum,
 11  rownum,
 12  rownum,
 13  'X',
 14  rownum,
 15  'X',
 16  'X',
 17  rownum,
 18  'X',
 19  'X',
 20  rownum,
 21  'X',
 22  'X',
 23  rownum,
 24  'X',
 25  'X',
 26  rownum,
 27  'X',
 28  rownum,
 29  rownum,
 30  rownum,
 31  rownum,
 32  rownum,
 33  rownum,
 34  rownum,
 35  'X',
 36  rownum,
 37  rownum,
 38  'X',
 39  rownum,
 40  'X',
 41  'X',
 42  rownum,
 43  'X',
 44  'X',
 45  rownum,
 46  'X',
 47  'X',
 48  rownum,
 49  'X',
 50  'X',
 51  rownum,
 52  'X',
 53  'X',
 54  rownum,
 55  'X',
 56  rownum,
 57  'X',
 58  rownum,
 59  rownum,
 60  rownum,
 61  'X',
 62  rownum,
 63  'X',
 64  rownum,
 65  rownum,
 66  rownum,
 67  rownum,
 68  rownum,
 69  rownum,
 70  rownum,
 71  sysdate,
 72  to_char(2015+trunc(rownum/100),'fm0000'),
 73  to_char(mod(rownum,12)+1,'fm00'),
 74  rownum,
 75  rownum
 76  from dual
 77  connect by level < 200;

199 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select distinct trn_yy, trn_mm from scott.app_branch order by 1,2;

TRN_ TR
---- --
2015 01
2015 02
2015 03
2015 04
2015 05
2015 06
2015 07
2015 08
2015 09
2015 10
2015 11
2015 12
2016 01
2016 02
2016 03
2016 04
2016 05
2016 06
2016 07
2016 08
2016 09
2016 10
2016 11
2016 12

24 rows selected.

SQL>

C:\>expdp scott/tiger dumpfile=subpar.dmp  directory=TEMP tables=APP_BRANCH:BRN_SUBPART_OCT15

Export: Release 12.2.0.1.0 - Production on Thu Apr 6 14:54:40 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** dumpfile=subpar.dmp directory=TEMP tables=APP_BRANCH:BRN_SUBPART_OCT15
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."APP_BRANCH":"BRN_PART_2015"."BRN_SUBPART_OCT15"  37.47 KB       8 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  C:\TEMP\SUBPAR.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Thu Apr 6 14:54:54 2017 elapsed 0 00:00:13


C:\>impdp scott/tiger dumpfile=subpar.dmp  directory=TEMP tables=APP_BRANCH table_exists_action=append remap_table=APP_BRANCH:APP_BRANCH1

Import: Release 12.2.0.1.0 - Production on Thu Apr 6 14:55:47 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** dumpfile=subpar.dmp directory=TEMP tables=APP_BRANCH table_exists_action=append remap_table=APP_BRANCH:APP_BRANCH1
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."APP_BRANCH1" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."APP_BRANCH1":"BRN_PART_2015"."BRN_SUBPART_OCT15"  37.47 KB       8 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Thu Apr 6 14:55:50 2017 elapsed 0 00:00:03


So try using the VERSION parameter to see if that works around the issue.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ORA-00904: "FIN_DATE": invalid identifier

Rajeshwaran, April 06, 2017 - 9:11 am UTC

Team - the above import log has this message in it.
....
ORA-02373: Error parsing insert statement for table "LIVEDB"."APP_BRANCH".
ORA-00904: "FIN_DATE": invalid identifier
....

looking at the DDL for the source table has this.
  PARSED_DATE      DATE,
  TRN_YY           VARCHAR2(4 BYTE),
  TRN_MM           VARCHAR2(2 BYTE),
  SOURCE           VARCHAR2(15 BYTE),
  FIN_DATE         VARCHAR2(10 BYTE) <<=====
)
TABLESPACE BRANCH
PCTUSED    0

looking at the DDL for the target table has this.
  PARSED_DATE      DATE,
  TRN_YY           VARCHAR2(4 BYTE),
  TRN_MM           VARCHAR2(2 BYTE),
  SOURCE           VARCHAR2(15 BYTE)
)
TABLESPACE BRANCH
PCTUSED    0

"FIN_DATE" column is missing in the target table.
Is that could be an issue here?
when tried to export and import from 11g(11.2.0.4) instance, don't see that missing column getting reported here.
D:\app\Vnameit\product\11.2.0\dbhome_1\BIN>expdp demo/demo@ora11g directory=TMP dumpfile=data_exp.dmp logfile=explog.txt tables=app_branch:BRN_SUBPART_OCT15

Export: Release 11.2.0.4.0 - Production on Thu Apr 6 14:25:51 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "DEMO"."SYS_EXPORT_TABLE_01":  demo/********@ora11g directory=TMP dumpfile=data_exp.dmp logfile=explog.txt tables=app_branch:BRN_SUBPART_OCT15
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DEMO"."APP_BRANCH":"BRN_PART_2015"."BRN_SUBPART_OCT15"  35.67 KB       8 rows
Master table "DEMO"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEMO.SYS_EXPORT_TABLE_01 is:
  D:\DATA_EXP.DMP
Job "DEMO"."SYS_EXPORT_TABLE_01" successfully completed at Thu Apr 6 14:27:12 2017 elapsed 0 00:01:01


D:\app\Vnameit\product\11.2.0\dbhome_1\BIN>impdp demo/demo@ora11g directory=TMP dumpfile=data_exp.dmp logfile=implog.txt remap_table=app_branch:app_branch1 table_exists_ac
tion=APPEND

Import: Release 11.2.0.4.0 - Production on Thu Apr 6 14:28:17 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DEMO"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEMO"."SYS_IMPORT_FULL_01":  demo/********@ora11g directory=TMP dumpfile=data_exp.dmp logfile=implog.txt remap_table=app_branch:app_branch1 table_exists_action=A
PPEND
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "DEMO"."APP_BRANCH1" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DEMO"."APP_BRANCH1":"BRN_PART_2015"."BRN_SUBPART_OCT15"  35.67 KB       8 rows
Job "DEMO"."SYS_IMPORT_FULL_01" successfully completed at Thu Apr 6 14:28:54 2017 elapsed 0 00:00:34

Connor McDonald
April 07, 2017 - 2:25 am UTC

Yes, but my point is - with the difference in columns, the import *still* works when you go from same version to same version.

Thats why I recommended the VERSION parameter, so they can hopefully get the same result.

My hypothesis is that FIN_DATE possibly got added with the new "fast add default" or similar, which is an 11g facility, and hence the 10g import doesn't understand some of the internal of the data pump file.

A reader, April 21, 2017 - 10:10 am UTC

Thanks sir.. It worked.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.