Getting ORA-01652 error when Run oracle sql query join into approx 10 tables -
i have 3 users on oracle , have same tablespace, when run following query getting following problem when executing query. , while executing takes hell lot of time :(
ora-01652: unable extend temp segment 128 in tablespace temp 01652. 00000 - "unable extend temp segment %s in tablespace %s" *cause: failed allocate extent of required number of blocks temporary segment in tablespace indicated. *action: use alter tablespace add datafile statement add 1 or more files tablespace indicated. query:
select a.* lea_agreement_dtl_rcl a, nbfc_customer_m b, db1.nbfc_product_m c, db1.lea_instrument_dtl e, db1.lea_loantype_m f, db1.nbfc_branch_m g, etrupti_cust_agrmnt_mapping h, (select a.caseid, sum ( case when b.chargecodeid = 82 , a.advicetype = 'r' nvl (a.adviceamt, 0) - nvl (a.txnadjustedamt, 0) - nvl (a.amtinprocess, 0) else 0 end) preemioverdue, sum ( case when b.chargecodeid = 9 nvl (a.adviceamt, 0) - nvl (a.txnadjustedamt, 0) - nvl (a.amtinprocess, 0) else 0 end) installmentoverdue, sum ( case when b.chargecodeid = 37 , a.advicetype = 'p' nvl (a.adviceamt, 0) - nvl (a.txnadjustedamt, 0) - nvl (a.amtinprocess, 0) else 0 end) unadjustedamount, sum ( case when b.chargecodeid = 8 nvl (a.adviceamt, 0) - nvl (a.txnadjustedamt, 0) - nvl (a.amtinprocess, 0) else 0 end) chequebounce, sum ( case when b.chargecodeid = 7 nvl (a.adviceamt, 0) - nvl (a.txnadjustedamt, 0) - nvl (a.amtinprocess, 0) else 0 end) lpp, sum ( case when b.chargecodeid not in (82,9) , a.advicetype = 'r' , a.bptype = 'ls' nvl (a.adviceamt, 0) - nvl (a.txnadjustedamt, 0) - nvl (a.amtinprocess, 0) else 0 end ) otheroverdue, sum ( case when a.advicetype = 'r' nvl (a.adviceamt, 0) - nvl (a.txnadjustedamt, 0) - nvl (a.amtinprocess, 0) else 0 end ) netreceivable, max (c.balancetenure) balancetenure db1.nbfc_txn_advice_dtl a, db1.nbfc_charges_m b, etrupti_cust_agrmnt_mapping d, (select x.agreementid, count (1) balancetenure, to_char (sum(nvl(princomp,0)),'999,999,999,999,999,900.00') db1.lea_repaysch_dtl x, etrupti_cust_agrmnt_mapping y x.billflage = 'n' , x.advflag ='n' , x.agreementid = y.agreementid , y.user_id = :user_id group x.agreementid ) c a.status = 'a' , a.chargeid = b.chargeid , a.caseid =c.agreementid , c.agreementid = d.agreementid , d.user_id = :user_id , a.ptxnadviceid null group a.caseid ) balances, (select w.agreementid, count(1) baltenure, to_char (sum(nvl(w.princomp,0)),'999,999,999,999,999,900.00') principalcomp db1.lea_repaysch_dtl w, etrupti_cust_agrmnt_mapping z w.billflage = 'n' , w.agreementid = z.agreementid , z.user_id = :user_id group w.agreementid ) repay a.lesseeid = b.customerid , balances.caseid(+)=a.proposalid , a.productflag = c.code(+) , a.proposalid = e.agreementid , c.loantype = f.loantype(+) , a.agreementid = repay.agreementid(+) , a.agreementid = h.agreementid , g.branchid(+) = a.service_branch , h.user_id = :user_id if somehow query can optimized suggestions welcomed.
try use cte. apart should use sql ansi-syntax, easier read.
you make cross join table db1.nbfc_parameter_m. intention?
with c (select x.agreementid, count (1) balancetenure, to_char (sum(nvl(princomp,0)),'999,999,999,999,999,900.00') db1.lea_repaysch_dtl x join etrupti_cust_agrmnt_mapping y on x.agreementid = y.agreementid x.billflage = 'n' , x.advflag ='n' , y.user_id = :user_id group x.agreementid ), balances (select a.caseid, sum ( case when b.chargecodeid = 82 , a.advicetype = 'r' nvl (a.adviceamt, 0) - nvl (a.txnadjustedamt, 0) - nvl (a.amtinprocess, 0) else 0 end) preemioverdue, sum ( case when b.chargecodeid = 9 nvl (a.adviceamt, 0) - nvl (a.txnadjustedamt, 0) - nvl (a.amtinprocess, 0) else 0 end) installmentoverdue, sum ( case when b.chargecodeid = 37 , a.advicetype = 'p' nvl (a.adviceamt, 0) - nvl (a.txnadjustedamt, 0) - nvl (a.amtinprocess, 0) else 0 end) unadjustedamount, sum ( case when b.chargecodeid = 8 nvl (a.adviceamt, 0) - nvl (a.txnadjustedamt, 0) - nvl (a.amtinprocess, 0) else 0 end) chequebounce, sum ( case when b.chargecodeid = 7 nvl (a.adviceamt, 0) - nvl (a.txnadjustedamt, 0) - nvl (a.amtinprocess, 0) else 0 end) lpp, sum ( case when b.chargecodeid not in (82,9) , a.advicetype = 'r' , a.bptype = 'ls' nvl (a.adviceamt, 0) - nvl (a.txnadjustedamt, 0) - nvl (a.amtinprocess, 0) else 0 end ) otheroverdue, sum ( case when a.advicetype = 'r' nvl (a.adviceamt, 0) - nvl (a.txnadjustedamt, 0) - nvl (a.amtinprocess, 0) else 0 end ) netreceivable, max (c.balancetenure) balancetenure db1.nbfc_txn_advice_dtl join db1.nbfc_charges_m b on a.chargeid = b.chargeid join c on a.caseid = c.agreementid join etrupti_cust_agrmnt_mapping d on c.agreementid = d.agreementid a.status = 'a' , d.user_id = :user_id , a.ptxnadviceid null group a.caseid ), repay (select w.agreementid, count(1) baltenure, to_char (sum(nvl(w.princomp,0)),'999,999,999,999,999,900.00') principalcomp db1.lea_repaysch_dtl w join etrupti_cust_agrmnt_mapping z on w.agreementid = z.agreementid w.billflage = 'n' , z.user_id = :user_id group w.agreementid ) select a.* lea_agreement_dtl_rcl join nbfc_customer_m b on a.lesseeid = b.customerid right outer join db1.nbfc_product_m c on a.productflag = c.code cross join db1.nbfc_parameter_m d -> there no join condition! purpose cross join? join db1.lea_instrument_dtl e on a.proposalid = e.agreementid right outer join db1.lea_loantype_m f on c.loantype = f.loantype left outer joindb1.nbfc_branch_m g on g.branchid = a.service_branch join etrupti_cust_agrmnt_mapping h on a.agreementid = h.agreementid left outer join balances on balances.caseid = a.proposalid right outer join repay on a.agreementid = repay.agreementid h.user_id = :user_id perhaps mixed right outer join , left outer join, not used anymore old oracle join syntax.
Comments
Post a Comment