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…
I have taken stored outline on UAT and production environment, and found that oracle is reading different index in production environment I checked by dropping the particular index in production and now the execution plan is same in both production and UAT environment but still the problem persists while refresh starts, temp tablespace is growing, when I monitor database oracle does an table scan of an table. and this operation is not completing.
Your kind help is need ASAP, Oracle version is 10.2.0.4
----------------MVIEW SCRIPT-----------------------------
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,