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.