Weird error mesages from query 1

[memarkiam]

Well, weird to me!

I'm running a fairly long SQL query (10 joined tables/views - details below). A similar query has run successfully before, but after adding another view, I get:

ORA-01114: IO error writing block to file 11 (block # 1044482)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 1044506)
Does anyone have any idea what this could be?

Many thanks,
Mark

The query is below:

select customer.CUSTOMER_ID,
customer.CUSTOMER_NO,
customer.TITLE,
customer.GENDER as Gndr,
customer.MARITAL_STATUS as MS,
customer.SEGMENTATION_VALUE_A as AdHldrs,
customer.SEGMENTATION_VALUE_B as Dpndts,
customer.SEGMENTATION_FLAG_A as StCrd,
customer.SEGMENTATION_FLAG_D as Cat,
customer.SEGMENTATION_FLAG_b as web,
address.ADDRESS_4 as Town,
address.ADDRESS_5 as County,
address.POST_CODE as PostCd,
customer.NO_MAILINGS,
customer.LAST_MAILING_DATE as LstMailDt,
TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) AS Age,
CASE
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) <=19 then '0-19 '
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) > 19 and TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) <= 24 then '20-24'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) >= 25 and TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) <= 29 then '25-29'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) >= 30 and TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) <= 34 then '30-34'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) >= 35 and TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) <= 39 then '35-39'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) >= 40 and TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) <= 44 then '40-44'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) >=45 then '45+ '
else ''
End as AgeRange,
case
when ML_POPDENS.DENSITY <= 500 then'Rural'
when ML_POPDENS.DENSITY >= 3000 then'Urban'
else 'Suburban'
end as Rgn,
customer.CREATE_DATE as StrtDt,
ml_churn.CHRN,
ml_rfmx_3.SCORE,
ml_rfmx_3.CILECODE,
CUSTOMER_LIFETIME_TOTALS.count_1 as Frq,
CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE as Rcncy,
CASE
WHEN TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) <= 30 then 'Last Month'
WHEN TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) >= 31 and TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) < 90 then '2-3 Months'
WHEN TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) >= 91 and TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) < 180 then '4-6 Months'
WHEN TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) >= 181 and TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) < 270 then '6-9 Months'
WHEN TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) >= 271 then '9+ Months'
END as RcncyGroup,
(CUSTOMER_LIFETIME_TOTALS.AMOUNT_1-CUSTOMER_LIFETIME_TOTALS.AMOUNT_2) as TotalNet,
CUSTOMER_LIFETIME_TOTALS.AMOUNT_1 as LifePurchVal,
CUSTOMER_LIFETIME_TOTALS.AMOUNT_2 as LifeReturnsVal,
CUSTOMER_LIFETIME_TOTALS.AMOUNT_3 as LifeRetailPurchVal,
CUSTOMER_LIFETIME_TOTALS.AMOUNT_4 as LifeRetailReturnsVal,
CUSTOMER_LIFETIME_TOTALS.AMOUNT_6 as LifeCatPurchVal,
CUSTOMER_LIFETIME_TOTALS.AMOUNT_7 as LifeCatReturnsVal,
CUSTOMER_LIFETIME_TOTALS.COUNT_1 as LifeTransCnt,
CUSTOMER_LIFETIME_TOTALS.COUNT_2 as LifeRetTransCnt,
CUSTOMER_LIFETIME_TOTALS.COUNT_3 as LifeRetVistis,
CUSTOMER_LIFETIME_TOTALS.COUNT_4 as LifeCatTransCnt,
CUSTOMER_LIFETIME_TOTALS.COUNT_3+CUSTOMER_LIFETIME_TOTALS.COUNT_4 as OrdrQty,
trunc(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) as AvTrnCnt,
TRUNC((CUSTOMER_LIFETIME_TOTALS.AMOUNT_1-CUSTOMER_LIFETIME_TOTALS.AMOUNT_2)/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),2) AS Velocity,
TRUNC((CUSTOMER_LIFETIME_TOTALS.AMOUNT_1-CUSTOMER_LIFETIME_TOTALS.AMOUNT_2)/(CUSTOMER_LIFETIME_TOTALS.COUNT_3+CUSTOMER_LIFETIME_TOTALS.COUNT_4),2) as AvOrdVal,
CASE
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 0 then '0 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 1 then '1 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 2 then '2 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 3 then '3 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 4 then '4 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 5 then '5 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 6 then '6 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 7 then '7 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.COUNT_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) >= 7 then '7+ '
else ''
END as Inte,
CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_AMOUNT as LstAmnt,
customer.STORE_NO as PrefStr,
customer.CREATE_SOURCE as CrtStr,
CUSTOMER_ATTRIBUTE.ATTRIBUTE_COMMENT as FrstPurchStre,
CUSTOMER_ATTRIBUTE.ATTRIBUTE_VALUE as FrstPurchVal,
CUSTOMER_ATTRIBUTE.ATTRIBUTE_DATE as FrstPurchDate,
ml_dpt_sls.KNITWEAR_202QTY,
ml_dpt_sls.T_SHIRTS_203QTY,
ml_dpt_sls.SMART_SHIRTS_204QTY,
ml_dpt_sls.FORMAL_SHRTS_205QTY,
ml_dpt_sls.COTTONS_206QTY,
ml_dpt_sls.LEATHERS_208QTY,
ml_dpt_sls.BEN_SHERMAN_209QTY,
ml_dpt_sls.SHORTS_210QTY,
ml_dpt_sls.CAS_BOTTOMS_211QTY,
ml_dpt_sls.CASUAL_JKTS_212QTY,
ml_dpt_sls.SMART_TRS_213QTY,
ml_dpt_sls.KICKERS_214QTY,
ml_dpt_sls.TIES_215QTY,
ml_dpt_sls.KANGOL_217QTY,
ml_dpt_sls.ACCESSORIES_220QTY,
ml_dpt_sls.CASUAL_SHIRT_221QTY,
ml_dpt_sls.SMART_JKTS_222QTY,
ml_dpt_sls.SHOES_223QTY,
ml_dpt_sls.JEEP_224QTY,
ml_dpt_sls.MIXERS_225QTY,
ml_dpt_sls.CAT_OTHER_BR_226QTY,
ml_dpt_sls.SWIMWEAR_101QTY,
ml_dpt_sls.BLOUSES_102QTY,
ml_dpt_sls.CAS_COTTONS_103QTY,
ml_dpt_sls.DRESSES_104QTY,
ml_dpt_sls.SOFT_CASUALS_105QTY,
ml_dpt_sls.FOOTWEAR_106QTY,
ml_dpt_sls.LINGERIE_107QTY,
ml_dpt_sls.HANDBAGS_108QTY,
ml_dpt_sls.GIFTS_109QTY,
ml_dpt_sls.BRANDS_110QTY,
ml_dpt_sls.KNITWEARQ_111TY,
ml_dpt_sls.ACCESSORIES_112QTY,
ml_dpt_sls.CASUAL_JKTS_113QTY,
ml_dpt_sls.CASUAL_BOTTS_115QTY,
ml_dpt_sls.PREMIUM_116QTY,
ml_dpt_sls.SUEDE_117QTY,
ml_dpt_sls.CASUAL_SHIRT_118QTY,
ml_dpt_sls.SKIRTS_119QTY,
ml_dpt_sls.TROUSERS_120QTY,
ml_dpt_sls.COATS_123QTY,
ml_dpt_sls.JACKETS_124QTY,
ml_dpt_sls.SMART_COTTON_125QTY,
ml_dpt_sls.JEWELLERY_126QTY,
ml_dpt_sls.DEFAULT_999QTY
from customer, CUSTOMER_LIFETIME_TOTALS, address, selection_888, ml_dpt_sls, ml_popdens, ml_monthly2, CUSTOMER_ATTRIBUTE, ml_rfmx_3, ml_churn
where selection_888.CUSTOMER_ID = customer.CUSTOMER_ID and
customer.CUSTOMER_ID = address.CUSTOMER_ID(+) and
customer.CUSTOMER_ID =+ CUSTOMER_LIFETIME_TOTALS.CUSTOMER_ID(+) and
customer.customer_ID = ml_dpt_sls.CUSTOMER_ID and
customer.CUSTOMER_ID = CUSTOMER_ATTRIBUTE.CUSTOMER_ID and CUSTOMER_ATTRIBUTE.ATTRIBUTE_GROUPING_CODE = 'FSTP' and
customer.customer_ID = ML_MONTHLY2.CUSTOMER_ID and
trim(substr(address.POST_CODE,1,4)) = ml_popdens.PCD and
customer.customer_ID = ml_rfmx_3.CUSTOMER_ID and
customer.CUSTOMER_ID = ml_churn.CUSTOMER_ID

 

[karluk]

As it happens, I got the same error recently. In my case the cause was that Oracle was attempting to expand my temporary tablespace datafile beyond the 4 gigabyte maximum size. I had foolishly turned on autoextend without specifying a maximum file size. Oracle kept on expanding the file until it was too large for the O/S to handle.

There's a good chance that the cause of your problem is similar. Check your datafiles and see if any of them are near the limit.

 

[karluk]

To be more precise, please check the size of file 11. Your ORA-01114 indicates that is the file which generates the error. The size of this file in megabytes is

select name,bytes/1024/1024 from v$datafile
where file#=11;

 

[memarkiam]

Thanks for the lead!

I'll go and check this now.

Cheers!!

Mark