Materialized View not refreshing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Materialized View not refreshing

  1. #1
    Join Date
    Oct 2007
    Posts
    4

    Materialized View not refreshing

    Hi,
    I created materialized view for complete refresh with the below syntax:

    CREATE MATERIALIZED VIEW xyz_mv
    TABLESPACE apps_ts_summary
    NOCACHE
    LOGGING
    NOCOMPRESS
    NOPARALLEL
    BUILD IMMEDIATE
    REFRESH COMPLETE
    NEXT (SYSDATE + 1)
    AS
    ....

    This involves count,union all...
    When I create this materialized view, it displays data but next day, this view is null.. it donot have any data..

    Not sure why as I have given sysdate+1 as next refresh date

    when I create the view with the below syntax

    CREATE MATERIALIZED VIEW XMCI_AP_PO_SCORECARD_MV
    TABLESPACE apps_ts_summary
    NOCACHE
    LOGGING
    NOCOMPRESS
    NOPARALLEL
    BUILD IMMEDIATE
    REFRESH COMPLETE
    START WITH SYSDATE
    NEXT (SYSDATE + 1)
    AS

    Then the view do not have any data after few seconds..

    Can anybody help me out as I am totally lost here.

    Thanks
    Ruma

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    what if you refresh manually and have you checked snp logs?

  3. #3
    Join Date
    Oct 2007
    Posts
    4
    when refreshing using exec dbms_mview.refresh(xyz_MV','c'); the data comes..

    I am using org striped views like po_headers etc hence first i set the set_client_info then I refresh else no data comes.

    Does this org striped views creating refresh problem..

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    if you are running Oracle E-Business Suite then you must set the environment in order to get data such as NLS_LANGUAGE, ORG ID etc

  5. #5
    Join Date
    Oct 2007
    Posts
    4
    How can you set these values in case trying to refresh automatically?

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    trace a user session and see what packages they run to set the variables

  7. #7
    Join Date
    Oct 2007
    Posts
    4
    Hi,

    I first created a materialized view that was based on the org striped views like po_headers hence the automatic refresh was not happening.
    After automatic complete refresh, the materialized view was showing null records.

    Hence I based the materialized view on "_all" tables and then based a view on this materialized view and this view I made org striped.

    In the materialized view , I was refering to ap_invoices_pkg.get_approval_status() this pkg internally uses org striped views like ap_invoices so I made a wrapper on top of this where I set the org context first and then I call the ap_invoices_pkg.

    This wrapper I call in my materialized view query..

    Is this approach correct in oracle apps.. I am doing this for the first time in apps..

    Thanks
    Ruma

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    sorry dont have Oracle eBiz to test that for you

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