Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Mview refresh


  • Please log in to reply

#1
avinesh

avinesh

    New Member

  • Member
  • Pip
  • 1 posts
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])
  • 0

Advertisements







Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP