Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    34

    Error : cannot read/modify an object after modifying it in parallel

    I am trying to run a stored procedure, but i am getting errors. So thought of checking part of code
    This is code i ran to check but getting some errors
    Can anyone please help

    EXECUTE IMMEDIATE 'Alter Session Enable Parallel DML';

    INSERT /*+ APPEND*/ INTO DWSGDW01.DWT00050_IMC_CCYYMM_FACT (
    SNAP_MO_YR_KEY_NO
    , IMC_KEY_NO
    , IMC_AFF_KEY_NO
    , IMC_CNTRY_KEY_NO
    , IMC_DERIVED_NM
    , IMC_ST_PROV_CD
    , IMC_POSTL_CD
    , IMC_TYPE_CD
    , ENGAGE_FLG
    , IMC_CURR_SEG_KEY_NO
    , IMC_APPL_DT_KEY_NO
    , LAST_RENEW_DT_KEY_NO
    , IMC_DLETE_DT_KEY_NO
    , SPON_IMC_KEY_NO
    , IMC_CLASS_CD
    , IMC_HIGH_AWD_KEY_NO
    , IMC_ACHV_AWD_KEY_NO
    , IMC_CURR_QUAL_AWD_KEY_NO
    , IMC_ISPON_FLG
    , IMC_ISPON_AFF_ID
    , IMC_LANG_CD
    , IMC_ISPON_IMC_KEY_NO
    , IMC_EXPIRE_DT_KEY_NO
    , FRONTLINE_CNT
    , LOM_DY_NO
    , LOA_KEY_NO
    , GLOBL_IMC_TYPE_KEY_NO
    , POSTL_CD_KEY_NO
    , STATUS_KEY_NO
    , LAST_ORD_OIMC_DT_KEY_NO
    , LAST_ORD_VIMC_DT_KEY_NO
    , LAST_SPON_DT_KEY_NO
    , LAST_UPDT_DT_KEY_NO
    , LAST_LOAD_DT_KEY_NO
    , LAST_UPDT_PROC_KEY_NO
    , LAST_LOAD_PROC_KEY_NO
    , LOS_GRP_KEY_NO
    , contb_distb_flg)
    SELECT /*+ PARALLEL (IMC 8)*/
    CAST(TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM') as NUMERIC (10,0))AS SNAP_MO_YR_KEY_NO
    , IMC.IMC_KEY_NO
    , IMC.IMC_AFF_KEY_NO
    , IMC.IMC_CNTRY_KEY_NO
    , IMC.IMC_NAME
    , NVL(CNTAC.PRM_STATE,CNTAC.PRM_PROV)
    , CNTAC.PRM_POST_CODE
    , IMC.IMC_TYPE_CD
    , CASE WHEN trim(DERV.ENGAGE_FLG) = 'Y' THEN 1 else 0 END
    , DERV.IMC_CURR_SEG_KEY_NO
    , IMC.APPL_DT_KEY_NO
    , DERV.RENEW_DT_KEY_NO
    , IMC.IMC_DLETE_DT_KEY_NO
    , IMC.SPON_IMC_KEY_NO
    , IMC.IMC_CLASS_CD
    , DERV.IMC_HIGH_AWD_KEY_NO
    , DERV.IMC_ACHV_AWD_KEY_NO
    , DERV.IMC_CURR_QUAL_AWD_KEY_NO
    , IMC.ISPON_FLG
    , IMC.ISPON_AFF_ID
    , CNTAC.PRM_LANG_CD
    , IMC.ISPON_IMC_KEY_NO
    , DERV.EXPIRE_DT_KEY_NO
    , DERV.FRONTLN_DISTB_CNT --FRONTLINE_CNT
    , CAST(case
    when (TO_DATE(TO_CHAR(last_day(ADD_MONTHS(SYSDATE,-1)),'YYYYMMDD'),'YYYYMMDD') - TO_DATE (IMC.APPL_DT_KEY_NO,'YYYYMMDD')) + 1 < 1 then 1
    else (TO_DATE(TO_CHAR(last_day(ADD_MONTHS(SYSDATE,-1)),'YYYYMMDD'),'YYYYMMDD') - TO_DATE (IMC.APPL_DT_KEY_NO,'YYYYMMDD')) + 1
    end AS NUMERIC (10,0)) as LOM_DY_NO
    , IMC.IMC_LOA_KEY_NO
    , IMC.GLOBL_IMC_TYPE_KEY_NO
    , CNTAC.POSTL_CD_KEY_NO
    , IMC.STATUS_KEY_NO
    , DERV.LAST_ORD_OIMC_DT_KEY_NO
    , DERV.LAST_ORD_VIMC_DT_KEY_NO
    , DERV.LAST_SPON_DT_KEY_NO
    , CAST(TO_CHAR(SYSDATE, 'YYYYMMDD')as numeric(10,0)) AS LAST_UPDT_DT_KEY_NO
    , CAST(TO_CHAR(SYSDATE, 'YYYYMMDD')as numeric(10,0)) as LAST_LOAD_DT_KEY_NO
    , CAST(-1 AS NUMERIC(10,0)) as LAST_UPDT_PROC_KEY_NO
    , CAST(-1 AS NUMERIC(10,0)) as LAST_LOAD_PROC_KEY_NO
    , IMC.LOS_GRP_KEY_NO
    , CASE WHEN DT.DT_KEY_NO IS NULL THEN 'N'
    ELSE 'Y' END AS CONTB_DISTB_FLG
    FROM DWSGDW01.DWT00740_IMC_DIM IMC, DWV00002_BUS_STAT_DIM BUS, DWT00410_IMC_DERV_DIM DERV, DWSGDW01.DWT00750_IMC_CNTAC_DIM CNTAC
    , (SELECT * FROM DWSGDW01.MV00002_DT_DIM WHERE MO_YR_KEY_NO = CAST ( TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') as NUMERIC (10,0))) DT
    where DERV.IMC_KEY_NO = IMC.IMC_KEY_NO and BUS.STATUS_KEY_NO = IMC.STATUS_KEY_NO AND CNTAC.IMC_KEY_NO = IMC.IMC_KEY_NO
    and ((APPL_DT_KEY_NO < to_char(last_day(SYSDATE),'YYYYMMDD')
    AND (IMC_DLETE_DT_KEY_NO = 19000101
    OR TO_CHAR(ADD_MONTHS(TO_DATE(IMC_DLETE_DT_KEY_NO, 'YYYYMMDD'),12),'YYYY-MM-DD') > to_char(last_day(SYSDATE),'YYYY-MM-DD') ))
    OR BUS.GLOBL_BUS_STAT_CD ='ACTIVE');


    Error Report

    SQL Error: ORA-12838: cannot read/modify an object after modifying it in parallel
    12838. 00000 - "cannot read/modify an object after modifying it in parallel"
    *Cause: Within the same transaction, an attempt was made to add read or
    modification statements on a table after it had been modified in parallel
    or with direct load. This is not permitted.
    *Action: Rewrite the transaction, or break it up into two transactions
    one containing the initial modification and the second containing the
    parallel modification operation.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,658
    what happens if/when the APPEND hint is removed/eliminated?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    Join Date
    Nov 2009
    Posts
    34
    Quote Originally Posted by anacedent View Post
    what happens if/when the APPEND hint is removed/eliminated?
    I did so

    INSERT INTO DWSGDW01.DWT00050_IMC_CCYYMM_FACT (
    SNAP_MO_YR_KEY_NO
    , IMC_KEY_NO
    , IMC_AFF_KEY_NO
    , IMC_CNTRY_KEY_NO
    , IMC_DERIVED_NM
    , IMC_ST_PROV_CD
    , IMC_POSTL_CD
    , IMC_TYPE_CD
    , ENGAGE_FLG
    , IMC_CURR_SEG_KEY_NO
    , IMC_APPL_DT_KEY_NO
    , LAST_RENEW_DT_KEY_NO
    , IMC_DLETE_DT_KEY_NO
    , SPON_IMC_KEY_NO
    , IMC_CLASS_CD
    , IMC_HIGH_AWD_KEY_NO
    , IMC_ACHV_AWD_KEY_NO
    , IMC_CURR_QUAL_AWD_KEY_NO
    , IMC_ISPON_FLG
    , IMC_ISPON_AFF_ID
    , IMC_LANG_CD
    , IMC_ISPON_IMC_KEY_NO
    , IMC_EXPIRE_DT_KEY_NO
    , FRONTLINE_CNT
    , LOM_DY_NO
    , LOA_KEY_NO
    , GLOBL_IMC_TYPE_KEY_NO
    , POSTL_CD_KEY_NO
    , STATUS_KEY_NO
    , LAST_ORD_OIMC_DT_KEY_NO
    , LAST_ORD_VIMC_DT_KEY_NO
    , LAST_SPON_DT_KEY_NO
    , LAST_UPDT_DT_KEY_NO
    , LAST_LOAD_DT_KEY_NO
    , LAST_UPDT_PROC_KEY_NO
    , LAST_LOAD_PROC_KEY_NO
    , LOS_GRP_KEY_NO
    , CONTB_DISTB_FLG)
    SELECT /*+ PARALLEL (IMC 8)*/
    CAST(TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM') as NUMERIC (10,0))AS SNAP_MO_YR_KEY_NO
    , IMC.IMC_KEY_NO
    , IMC.IMC_AFF_KEY_NO
    , IMC.IMC_CNTRY_KEY_NO
    , IMC.IMC_NAME
    , NVL(CNTAC.PRM_STATE,CNTAC.PRM_PROV)
    , CNTAC.PRM_POST_CODE
    , IMC.IMC_TYPE_CD
    , CASE WHEN trim(DERV.ENGAGE_FLG) = 'Y' THEN 1 else 0 END
    , DERV.IMC_CURR_SEG_KEY_NO
    , IMC.APPL_DT_KEY_NO
    , DERV.RENEW_DT_KEY_NO
    , IMC.IMC_DLETE_DT_KEY_NO
    , IMC.SPON_IMC_KEY_NO
    , IMC.IMC_CLASS_CD
    , DERV.IMC_HIGH_AWD_KEY_NO
    , DERV.IMC_ACHV_AWD_KEY_NO
    , DERV.IMC_CURR_QUAL_AWD_KEY_NO
    , IMC.ISPON_FLG
    , IMC.ISPON_AFF_ID
    , CNTAC.PRM_LANG_CD
    , IMC.ISPON_IMC_KEY_NO
    , DERV.EXPIRE_DT_KEY_NO
    , DERV.FRONTLN_DISTB_CNT --FRONTLINE_CNT
    , CAST(case
    when (TO_DATE(TO_CHAR(last_day(ADD_MONTHS(SYSDATE,-1)),'YYYYMMDD'),'YYYYMMDD') - TO_DATE (IMC.APPL_DT_KEY_NO,'YYYYMMDD')) + 1 < 1 then 1
    else (TO_DATE(TO_CHAR(last_day(ADD_MONTHS(SYSDATE,-1)),'YYYYMMDD'),'YYYYMMDD') - TO_DATE (IMC.APPL_DT_KEY_NO,'YYYYMMDD')) + 1
    end AS NUMERIC (10,0)) as LOM_DY_NO
    , IMC.IMC_LOA_KEY_NO
    , IMC.GLOBL_IMC_TYPE_KEY_NO
    , CNTAC.POSTL_CD_KEY_NO
    , IMC.STATUS_KEY_NO
    , DERV.LAST_ORD_OIMC_DT_KEY_NO
    , DERV.LAST_ORD_VIMC_DT_KEY_NO
    , DERV.LAST_SPON_DT_KEY_NO
    , CAST(TO_CHAR(SYSDATE, 'YYYYMMDD')as numeric(10,0)) AS LAST_UPDT_DT_KEY_NO
    , CAST(TO_CHAR(SYSDATE, 'YYYYMMDD')as numeric(10,0)) as LAST_LOAD_DT_KEY_NO
    , CAST(-1 AS NUMERIC(10,0)) as LAST_UPDT_PROC_KEY_NO
    , CAST(-1 AS NUMERIC(10,0)) as LAST_LOAD_PROC_KEY_NO
    , IMC.LOS_GRP_KEY_NO
    , CASE WHEN DT.DT_KEY_NO IS NULL THEN 'N'
    ELSE 'Y' END AS CONTB_DISTB_FLG
    FROM DWSGDW01.DWT00740_IMC_DIM IMC, DWV00002_BUS_STAT_DIM BUS, DWT00410_IMC_DERV_DIM DERV, DWSGDW01.DWT00750_IMC_CNTAC_DIM CNTAC
    , (SELECT * FROM DWSGDW01.MV00002_DT_DIM WHERE MO_YR_KEY_NO = CAST ( TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') as NUMERIC (10,0))) DT
    where DERV.IMC_KEY_NO = IMC.IMC_KEY_NO and BUS.STATUS_KEY_NO = IMC.STATUS_KEY_NO AND CNTAC.IMC_KEY_NO = IMC.IMC_KEY_NO
    and ((APPL_DT_KEY_NO < to_char(last_day(SYSDATE),'YYYYMMDD')
    AND (IMC_DLETE_DT_KEY_NO = 19000101
    OR TO_CHAR(ADD_MONTHS(TO_DATE(IMC_DLETE_DT_KEY_NO, 'YYYYMMDD'),12),'YYYY-MM-DD') > to_char(last_day(SYSDATE),'YYYY-MM-DD') ))
    OR BUS.GLOBL_BUS_STAT_CD ='ACTIVE');

    Error Report

    SQL Error: ORA-00001: unique constraint (DWSGDW01.DWI00050CC_SNAP_MO_YR_K_IMC_K) violated
    00001. 00000 - "unique constraint (%s.%s) violated"
    *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
    For Trusted Oracle configured in DBMS MAC mode, you may see
    this message if a duplicate entry exists at a different level.
    *Action: Either remove the unique restriction or do not insert the key.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,658
    http://www.dbforums.com/oracle/16512...ml#post6438939

    It is de'javu all over again.

    We have met the enemy, & they is us.

    UNIQUE constraint does not allow duplicate key values.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •