[avinesh]

hi everyone...while trying to refresh an materialized view.. oracle throws cannot extend temp table space error.. while starting to refresh mivew temp table space is empty but once refresh started temp tablespace is growing and throws cannot extend temp tablespace error,,,size of temp tablesapce is 200GB..when i monter the session it does an sort event of an table(ammt_pol_ag_comm).. only 4% of this sort event is completing after that it throws error bu occupying the entire 200 GB tabespace.. please find the mview script below..

CREATE materialized VIEW ammv_agent_pol_persis_emas
NoLogging
Parallel 10
Build Immediate
Refresh on demand
With Primary Key
AS
SELECT /*+ PARALLEL(a 10) PARALLEL(b 10)*/ a.v_policy_no,b.n_agent_no,c.v_agent_code,c.n_channel_no,c.v_rank_code,b.v_rank_code v_prod_level,d.v_prod_line,
e.n_persis_months,e.v_calc_method,e.d_business_block_from, e.d_business_block_to,
SUM((n_net_contribution*e.n_persis_months)/NVL(TO_NUMBER(v_pymt_freq),1)) n_full_beap,
SUM(bpg_agency_persistency_mcis.bfn_get_persis_prem(a.v_policy_no, a.d_commencement, e.d_run_as_at, a.v_pymt_freq, a.d_prem_due_date,

a.n_net_contribution, a.v_cntr_stat_code, e.n_persis_months, SYSDATE,nvl(e.v_prem_chng_appl,'N'),'E')) n_expected_beap,
SUM(bpg_agency_persistency_mcis.bfn_get_persis_prem(a.v_policy_no, a.d_commencement, e.d_run_as_at, a.v_pymt_freq, a.d_prem_due_date,

a.n_net_contribution, a.v_cntr_stat_code, e.n_persis_months, SYSDATE,nvl(e.v_prem_chng_appl,'N'),'A')) n_actual_received,
SUM(bpg_agency_persistency_mcis.bfn_get_persis_prem(a.v_policy_no, a.d_commencement, e.d_run_as_at, a.v_pymt_freq, a.d_prem_due_date,

a.n_net_contribution, a.v_cntr_stat_code, e.n_persis_months, SYSDATE,nvl(e.v_prem_chng_appl,'N'),'LE')) n_lapse_expected,
SUM(bpg_agency_persistency_mcis.bfn_get_persis_prem(a.v_policy_no, a.d_commencement, e.d_run_as_at, a.v_pymt_freq, a.d_prem_due_date,

a.n_net_contribution, a.v_cntr_stat_code, e.n_persis_months, SYSDATE,nvl(e.v_prem_chng_appl,'N'),'LA')) n_lapse_received,
SUM(bpg_agency_persistency_mcis.bfn_get_persis_prem(a.v_policy_no, a.d_commencement, e.d_run_as_at, a.v_pymt_freq, a.d_prem_due_date,

a.n_net_contribution, a.v_cntr_stat_code, e.n_persis_months, SYSDATE,nvl(e.v_prem_chng_appl,'N'),'EC')) n_expected_count,
SUM(bpg_agency_persistency_mcis.bfn_get_persis_prem(a.v_policy_no, a.d_commencement, e.d_run_as_at, a.v_pymt_freq, a.d_prem_due_date,

a.n_net_contribution, a.v_cntr_stat_code, e.n_persis_months, SYSDATE,nvl(e.v_prem_chng_appl,'N'),'AC')) n_actual_count,


a.d_commencement,a.d_prem_due_date,a.v_cntr_stat_code,a.v_pymt_freq,a.v_pmt_method_code,c.V_BRANCH_CODE,a.V_PLAN_CODE,a.n_net_contribution,d_run_date,n_sum_covered,a.d_issue

FROM gnmt_policy a,
ammt_pol_ag_comm b,
ammm_agent_master c,
gnmm_plan_master d,
amdt_persistency_run_dates e,
ammm_persistency_policy_status f
WHERE a.v_policy_no = b.v_policy_no
AND b.n_agent_no = c.n_agent_no
AND a.v_plan_code = d.v_plan_code
AND c.n_channel_no = e.n_channel_no
AND d.v_prod_line = f.v_persis_lob_code
AND a.d_issue BETWEEN e.d_business_block_from AND e.d_business_block_to
AND e.d_run_as_at BETWEEN f.d_effective_from AND NVL(f.d_effective_to, SYSDATE)
AND EXISTS (SELECT 1 FROM ammt_persistency_policy_status g
WHERE g.v_persis_seq_no = f.v_persis_seq_no
AND g.v_policy_status = a.v_cntr_stat_code
AND g.v_persis_factor IN('EXP-PREM', 'ACT-PREM'))
AND a.v_pymt_freq != '00'
AND a.v_grp_ind_flag='I'
AND b.v_status = 'A'
AND f.v_status = 'A'
AND e.v_status = 'A'
AND TRUNC(e.d_run_date) = (select TRUNC(max(d_run_date)) from amdt_persistency_run_dates where v_status='A' and v_calc_method='F' AND N_PERSIS_MONTHS IS NOT NULL)
AND a.d_commencement IS NOT NULL
AND a.d_prem_due_date IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM ammt_persis_plans_excluded WHERE v_plan_code=d.v_plan_code AND v_lob_code=d.v_prod_line)
GROUP BY a.v_policy_no,b.n_agent_no,c.v_agent_code,c.n_channel_no,c.v_rank_code,b.v_rank_code,d.v_prod_line,
e.n_persis_months,e.v_calc_method,e.d_business_block_from, e.d_business_block_to
,a.d_commencement,a.d_prem_due_date,a.v_cntr_stat_code,a.v_pymt_freq,a.v_pmt_method_code,c.V_BRANCH_CODE,a.V_PLAN_CODE
,a.n_net_contribution,d_run_date,n_sum_covered,a.d_issue;


Thank you for any inputs,


Regards
Avinesh ([email protected])