Materialized view not getting refreshed
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Materialized view not getting refreshed

  1. #1
    Join Date
    Dec 2002
    Posts
    32

    Materialized view not getting refreshed

    Hi,
    I've created a Mview which is not getting rereshed. I would like to refresh it every hour. It is a compled MV with a union and uses a view in join.
    The MV is
    CREATE MATERIALIZED VIEW trn_dependent_mv
    REFRESH NEXT SYSDATE + 1/24 AS
    SELECT
    elg.txn_id TRAN_SEQ,
    dep.chg_reason REASON
    FROM dependent dep,
    transaction elg,
    user_vw uv
    WHERE elg.txn_id=dep.txn_id
    AND uv.gessouid = elg.gessouid
    UNION
    SELECT
    elg.txn_id TRAN_SEQ ,
    dep.chg_reason REASON
    FROM dependent dep,
    transaction_tv elg,
    user_vw uv
    WHERE elg.txn_id=dep.txn_id
    AND uv.uid = elg.uid;

    Any problems in it?
    Sajeev

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    Post relevent error messages from alertlog. How many SNP processes do you have? What version? Does manual refresh work? Does the schema scheduling the job have privileges to refresh?
    Last edited by Axr2; 06-09-2004 at 03:06 PM.

  3. #3
    Join Date
    Dec 2002
    Posts
    32
    There are no error messages in the alert log. We have the default value 0 for the SNP process. The version is 8.1.7.4 and manual refresh using the dbms_refresh.refresh.. works...
    The schema doesn't have any additional privileges to refresh..

    thanks
    Sajeev

  4. #4
    Join Date
    Oct 2002
    Posts
    807
    Well, if job_queue_processes is 0, how do you expect your job to run?

  5. #5
    Join Date
    Dec 2002
    Posts
    32
    That was the problem. it is working after changing job_queue_processes ..
    thanks a lot
    Sajeev

  6. #6
    Join Date
    Oct 2002
    Posts
    807
    RTConceptsM before venturing. Lots of useful stuff in there. That's materialized views 101. Sorry to preach.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width