1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Mview Refresh

Discussion in 'SQL PL/SQL' started by avineshr, Sep 16, 2012.

  1. avineshr

    avineshr Guest

    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 (avineshr@gmail.com)
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You don't have enough space in your temp tablespace to refresh this materialized view; a 'refresh on demand' is a complete refresh so it's replacing all of the data, not just those records that have changed. If possible you should recreate this materialized view as a fast refresh mview, creating materialized view logs on the base tables (presuming, of course, the join creates key-preserved records). If a fast refresh is not possible (such as Oracle won't allow it because of the nature of the joins) you will need to extend your temp tablespace quite a bit. You will continue to receive this error until you use a different method (fast) to refresh the view (again, IF that is possible) or you extend your temp tablespace, probably into the TB range.