Hi All,
I have created the materilazied view which will run every month first saturday. I have deployed in production as well. for last saturday it cause temp space issue no other batches couldn't ran at the time.
Can you please advise how to clear the tempspace issue and what is the reason behind for causing the issue.
PFB Query.
CREATE MATERIALIZED VIEW MV_SPRING
REFRESH COMPLETE ON DEMAND
AS
SELECT SPR.DATADATE,
SPR.BRANCHID,
SPR.COUNTER,
SPR.GL,
SPR.CATEGORY,
SPR.SYSTEMID,
SPR.COSTCENTRE,
SPR.CCY,
SPR.CIF,
SPR.CUSTOMERNAME,
SPR.CUSTOMERIND,
SPR.CONTRACTNUMBER,
SPR.DEALBALANCE,
SPR.USDBALANCE,
SPR.GBPBALANCE,
SPR.LCYBALANCE,
SPR.PBRE,
SPR.PBRS,
SPR.ASIANFLAG,
SPR.GROUPCIF,
SPR.ROLLDATE,
SPR.DEALDATE,
SPR.EXCEPTIONALPRICEFLAG,
SPR.INSERTDATE,
SPR.BUSINESSUNIT,
SPR.PRODUCTCODE,
SPR.CUSTOMERCLASS,
SPR.DEPARTMENTID,
SPR.OPERATINGUNIT,
SPR.CONTRACTSTARTDATE,
SPR.INTERESTPAYMENTFREQUENCY,
SPR.INITIALAMOUNT,
SPR.AFFILIATIONCODE,
SPR.UNITQUANTITY,
SPR.UNSETTLED_NOMINAL,
SPR.LOAN_END_USE,
SPR.IA1,
SPR.IA2,
SPR.MANAGEMENT_FEE_PERCENTAGE,
SPR.MANAGEMENT_FEE,
SPR.TRAILER_FEE_PERCENTAGE,
SPR.TRAILER_FEE,
SPF.CUSTOMERLIAB,
SPF.SECTOR,
SPF.CUSTOMERRESIDENCY,
SPF.CUSTOMERDOMICILE,
SPF.CUSTOMERNATIONALITY,
SPF.VALDATE,
SPF.MATDATE,
SPF.COYMNE,
SPF.RMPRIMARY,
SPF.CLIENTSET,
SPF.RMREFERRAL,
SPF.PRODUCTNAME,
SPF.INTERESTRATE,
SPF.TRANSFERPRICINGRATE,
SPF.BLOCKCODE,
SPF.IWBCIF,
SPF.INTERESTDUEDATE,
SPF.PLEDGECUSTOMER,
SPF.ACCOUNTNUMBER "FIN_ACCOUNTNUMBER" ,
GA.ACCOUNTNUMBER "AC_ACCOUNTNUMBER",
GA.CUSTOMER,
GA.ACCOUNTTITLE1,
GA.ACCOUNTTITLE2,
GA.SHORTTITLE,
GA.MNEMONIC,
GA.POSITIONTYPE,
GA.CURRENCY,
GA.CURRENCYMARKET,
GA.LIMITREF,
GA.ACCOUNTOFFICER,
GA.OTHEROFFICER,
GA.POSTINGRESTRICT,
GA.RECONCILEACCT,
GA.INTERESTLIQUACCT,
GA.INTERESTCOMPACCT,
GA.INTNOBOOKING,
GA.REFERALCODE,
GA.WAIVELEDGERFEE,
GA.CONDITIONGROUP,
GA.INACTIVMARKER,
GA.OPENACTUALBAL,
GA.OPENCLEAREDBAL,
GA.ONLINEACTUALBAL,
GA.ONLINECLEAREDBAL,
GA.WORKINGBALANCE,
GA.DATELASTCRCUST,
GA.AMNTLASTCRCUST,
GA.TRANLASTCRCUST,
GA.DATELASTCRAUTO,
GA.AMNTLASTCRAUTO,
GA.TRANLASTCRAUTO,
GA.DATELASTCRBANK,
GA.AMNTLASTCRBANK,
GA.TRANLASTCRBANK,
GA.DATELASTDRCUST,
GA.AMNTLASTDRCUST,
GA.TRANLASTDRCUST,
GA.DATELASTDRAUTO,
GA.AMNTLASTDRAUTO,
GA.TRANLASTDRAUTO,
GA.DATELASTDRBANK,
GA.AMNTLASTDRBANK,
GA.TRANLASTDRBANK,
GA.CAPBACKVALUE,
GA.PASSBOOK,
GA.STARTYEARBAL,
GA.OPENINGDATE,
GA.OPENCATEGORY,
GA.OPENVALDATEDBAL,
GA.CONTINGENTBALCR,
GA.CONTINGENTBALDR,
GA.ACCOUNTCREDITINT,
GA.ACCOUNTDEBITINT,
GA.CLOSUREDATE,
GA.PREVOPENBAL,
GA.CHARGEACCOUNT,
GA.CHARGEMKT,
GA.INTERESTCCY,
GA.INTERESTMKT,
GA.ALLOWNETTING,
GA.OUREXTACCTNO,
GA.GENCOSTCENTRE,
GA.LOCKAMTEXPDT,
GA.BLKREASONCODE,
GA.RIBKEY,
GA.HOLDDESC,
GA.ACCT_BAL_STERLING,
GA.AUTH_NEGATIVE_BAL,
GA.EXCHANGE_RATE,
GA.INTEREST_ACCRUED_ACCT_CURR,
GA.INTEREST_ACCRUED_GBP,
GA.ELIGIBLE_DGSD,
GA.PRODUCT_NAME
FROM T_PBAMLGLOBUSSPRING SPR,
T_PBAMLGLOBUSSPRING_FINANCIAL SPF,
T_PBAMLGLOBUSACCT_HIST GA
WHERE (SPR.DATADATE >=
TO_CHAR(TRUNC(ADD_MONTHS(TRUNC(SYSDATE), -12), 'YEAR'), 'YYYYMMDD') AND /* Starting day of the previous year */
SPR.DATADATE <= TO_CHAR(TRUNC(SYSDATE), 'YYYYMMDD')) /* Sysdate */
AND SPR.DATADATE = SPF.DATADATE
AND SPR.BRANCHID = SPF.BRANCHID
AND SPR.COUNTER = SPF.COUNTER
AND SPF.BRANCHID = GA.BRANCHID(+)
AND SPF.ACCOUNTNUMBER = GA.ACCOUNTNUMBER(+);