1 2 Previous Next 24 Replies Latest reply: Mar 23, 2013 4:18 AM by Jonathan Lewis RSS

    SQL keeps erroring out with tablespace issue

    user7202581
      CREATE TABLE /*+ PARALLEL(CLAIM_LINE_OASIS_PK_TMP, 16) */ EDWSTG.CLAIM_LINE_OASIS_PK_TMP NOLOGGING AS
      SELECT /*+ PARALLEL(B, 16) PARALLEL(C, 16) */
      B.CLM_LN_EDW_SK,
      C.ICN_NBR,
      C.CLM_LN_NBR,
      C.ADJ_CD,
      C.CLM_ORIG_CD,
      C.CLM_HDR_EDW_SK,
      C.CLM_ORIG_CD_SK,
      C.ADJ_CD_SK,
      C.FRST_SVC_DT_SK,
      C.LST_SVC_DT_SK,
      C.UNIT_OF_SVC_CNT,
      C.BSC_POS_CD_SK,
      C.BSC_POS_CD,
      C.STD_POS_SK,
      C.STD_POS_CD,
      C.BRD_SVC_CATEG_CD_SK,
      C.BRD_SVC_CATEG_CD,
      C.TOS_CD_SK,
      C.TOS_CD,
      C.PD_OR_DENY_CD_SK,
      C.PD_OR_DENY_CD,
      C.PROF_PROC_CD_EDW_SK,
      C.PROF_PROC_CD,
      C.FAC_HCPCS_CD_EDW_SK,
      C.FAC_HCPCS_CD,
      C.MOD_1_CD_EDW_SK,
      C.MOD_1_CD,
      C.MOD_2_CD_EDW_SK,
      C.MOD_2_CD,
      C.MOD_3_CD_EDW_SK,
      C.MOD_3_CD,
      C.MOD_4_CD_EDW_SK,
      C.MOD_4_CD,
      C.RVNU_CD_EDW_SK,
      C.RVNU_CD,
      C.BIL_AMT,
      C.ALLOW_AMT,
      C.PD_AMT,
      C.DED_AMT,
      C.COINS_AMT,
      C.COPAY_AMT,
      C.SEC_ALLOW_AMT,
      C.HMO_OOA_IND,
      C.APPRV_TO_PAY_AMT,
      C.NON_COV_REJ_AMT,
      C.OTHR_INSR_LIAB_AMT,
      C.TOTH_NBR,
      C.TOTH_Q_CD_SK,
      C.CLM_GRP_BNF_EDW_SK,
      C.CLM_SBGRP_EDW_SK,
      C.CLM_MBR_EDW_SK,
      C.CLM_MBR_BNF_EDW_SK,
      C.CLM_BIL_PROV_EDW_SK,
      C.CLM_ATTND_PROV_EDW_SK,
      C.CLM_REF_PROV_EDW_SK,
      C.CLM_BIL_FIL_PROV_EDW_SK,
      C.CLM_OPER_PROV_EDW_SK,
      C.CLM_SVC_FAC_LOC_PROV_EDW_SK,
      C.CLM_RX_PROV_EDW_SK,
      C.CLM_PHARM_PROV_EDW_SK,
      C.BIL_PROV_EDW_SK,
      C.REF_PROV_EDW_SK,
      C.ATTND_PROV_EDW_SK,
      C.CAP_FAC_PROV_EDW_SK,
      C.AUTH_PROV_EDW_SK,
      C.LBL_PROV_EDW_SK,
      C.IPA_PROV_EDW_SK,
      C.REF_TO_PROV_EDW_SK,
      C.PCP_PROV_EDW_SK,
      C.OPER_PROV_EDW_SK,
      C.SVC_FAC_LOC_PROV_EDW_SK,
      C.SBGRP_EDW_SK,
      C.MBR_EDW_SK,
      C.MBR_BNF_EDW_SK,
      C.AGE_EDW_SK,
      C.CLM_LN_ITM_IDS_SK,
      C.SS_CD_SK,
      C.SS_CD,
      C.EDW_INSRT_DT,
      C.EDW_UPDT_DT,
      C.EDW_NAT_KEY_TXT,
      C.CREAT_RUN_CYC_EXEC_SK,
      C.LST_UPDT_RUN_CYC_EXEC_SK,
      C.CRC_NBR,
      C.HCPCS_PROC_CD,
      C.DIAG_CATEG_CD,
      C.CLM_CAPITN_IA_CLM_EDW_SK,
      C.CLM_SPEC_EDW_SK,
      C.CLM_CLS_CD,
      C.CLM_PROC_CD,
      C.CLM_CAPITN_IA_GRP_CAPTR_EDW_SK,
      C.CLM_CAPITN_IA_MBR_CAPTR_EDW_SK,
      C.CLM_CAPITN_IA_BIL_PROV_EDW_SK,
      C.CLM_CAPITN_IA_IPA_PROV_EDW_SK,
      C.CLM_CAPITN_ATTND_PROV_EDW_SK,
      C.CLM_CAPITN_IA_PCP_EDW_SK,
      C.CLM_CAPITN_CUR_GRP_EDW_SK,
      C.CLM_CAPITN_CUR_IA_MBR_EDW_SK,
      C.GRP_CAPTR_BIL_UNT_ID,
      C.GRP_CAPTR_BNF_PKG_CD,
      C.GRP_CAPTR_BNF_TYP_CD,
      C.GRP_CAPTR_CAC_CD,
      C.GRP_CAPTR_EDW_SK,
      C.GRP_CAPTR_GRP_ID,
      C.GRP_CAPTR_SBGRP_NM,
      C.GRP_CAPTR_SBGRP_TYP_CD,
      C.MBR_CAPTR_BTH_DT,
      C.MBR_CAPTR_EDW_SK,
      C.MBR_CAPTR_GNDR_CD,
      C.MBR_CAPTR_MBR_ID,
      C.MBR_CAPTR_SBSCR_ALTN_ID,
      C.MBR_CAPTR_SBSCR_RGN_CD,
      C.MBR_CAPTR_SBSCR_ZIP_CD,
      C.SUB_FRST_NM,
      C.MBR_FRST_NM,
      C.SUB_LST_NM,
      C.MBR_LST_NM,
      C.SUB_MID_INIT,
      C.MBR_MID_INIT,
      C.AGE_MO,
      C.MBR_GRP_ID,
      C.MBR_GRP_BIL_UNT_ID,
      C.MBR_SS_CD,
      C.MBR_ID,
      C.MBR_SBSCR_ALTN_ID,
      C.FUND_POOL_EDW_SK,
      C.COB_SAV_AMT,
      C.BNF_COV_CD,
      C.BNF_CATEG_CD,
      C.ACCT_MO_SK,
      C.BNF_SET_EDW_SK,
      C.CAPITN_CLM_FUND_POOL_CD,
      C.LOS_DY_CNT
      FROM EDWXREF.CLAIM_LINE_XREF B, EDWSTG.CLAIM_LINE_OASIS C
      WHERE C.ICN_NBR = B.ICN
      AND C.ADJ_CD = B.ADJ_CD
      AND C.CLM_ORIG_CD= B.CLM_ORIG_CD
      AND C.CLM_LN_NBR = B.CLM_LN_NBR

      We keep getting this error.
      FROM EDWXREF.CLAIM_LINE_XREF B, EDWSTG.CLAIM_LINE_OASIS C
      *
      ERROR at line 136:
      ORA-01652: unable to extend temp segment by 2048 in tablespace EDWSTGDATA00

      Is there something that can be done?Is there a way to estimate the space required for this SQL?

      Regards,
      Narayan
        1 2 Previous Next