-
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
-
what if you refresh manually and have you checked snp logs?
-
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..
-
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
-
How can you set these values in case trying to refresh automatically?
-
trace a user session and see what packages they run to set the variables
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|