Replication :: Cannot Fast Refresh Truncated MV Even After Complete Refresh
Aug 14, 2010
I am getting ORA -32320 when I tried to fast fresh MVs that were truncated.I am able to complete refresh all MVs. However, some MVs are still giving me ORA-32320 when I try to do fast refresh even after complete refresh.
DBMS_MVIEW.EXPLAIN_PLAN says the MVIEW can be fast refreshed.
ERROR at line 1:
ORA-32320: REFRESH FAST of "dev"."Claim_line" unsupported after cointainer table
PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1
Quote:ORA-32320:
REFRESH FAST of "string"."string" unsupported after container table PMOPs Cause: A Partition Maintenance Operation (PMOP) has been performed on the materialized view, and no materialized view supports fast refersh after container table PMOPs. Action: Use REFRESH COMPLETE. Note: you can determine why your materialized view does not support fast refresh after PMOPs using the DBMS_MVIEW.EXPLAIN_MVIEW() API.
I am using basic replication in oracle 10.2.0.3. My materialized view object contains around 3 million records. Usually we are using FAST refresh but in my materialized view having some problem that’s the reason I am going to Complete refresh.
If I start complete refresh it will take 8-12 hours to complete refresh.
My Question is: During Complete refresh how to monitor complete refresh running or not except using
v$mvrefresh.
Is there any way to monitor complete refresh because suppose target database not available/network problem/ dead lock occur in my database. I can’t able to track refresh.
CREATE Materialized View log on t1 with rowid; CREATE Materialized View log on t2 with rowid; CREATE Materialized View log on t3 with rowid; CREATE Materialized View MV_test BUILD IMMEDIATE FAST REFRESH ON COMMIT [code].......
I have created a Materialized View by joining the above 3 tables. The Materialized View logs with ROWID has been created on each of the 3 base tables. One of the base tables has around 160 million rows and i expect the Materialized View to have the same number of rows.
The Materialized View has been built for FAST REFRESH on COMMIT. The SELECT list of the CREATE Materialized View statement includes all the ROWIDS from each base tables for doing the fast refresh.
The DBMS_MVIEW explain view shows that the view is capable of doing fast refresh on any DML. The Materialized View took 3 hrs to get built. I have also created indexes on all the three rowid columns (t1rowid,t2rowid,t3rowid) on the Materialized View.
Now, When i do any update on one of the base table and do a commit,
UPDATE t1 set value=500 where tx_id='1234';
The commit is taking around around 15 minutes. I have generated the trace and had a look at it, and it shows that, to refresh the Materialized View, ORACLE is first deleting that record from the Materialized View and then inserting the new record from the base table to the Materialized View. This delete done by oracle is being done on the basis of the rowid of the updated record stored on the Materialized View log,
However its not using the index created on the t1rowid of the Materialized View and doing a full scan of the MV and hence the delete is taking a lot of time. The delete statement is using a hint NO_MERGE and HASH_SJ which is causing it to do the full table scan of the MV. why is ORACLE not using the index on the t1rowid column of the MV to do this delete?
Also to do the insert in the MV, its selecting the new changed value from the base tables t1,t2 and t2. Here again its doing a full table scan of the base table t1, rather then using the rowid directly or the index coulmn.which is taking a lot of time.
We are facing serious problem while refreshing materialized views using fast refresh option in ORACLE..For the very first time we are performing Complete refresh of data from DB1 to DB2 for few tables.Ongoing we are performing Fast Refresh.Sometimes the fast refresh works fine without any error and sometimes it fails with the below error.
ERROR at line 1: ORA-32320: REFRESH FAST of "CIR"."C_BO_COMM" unsupported after cointainer table PMOPs ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841 ORA-06512: at line 1
let me know when will this error occur during Fast Refresh.
/* Step2 Create MATERIALIZED VIEW Refresh after Every 20 Minutes */ CREATE MATERIALIZED VIEW MV_ABC REFRESH FAST START WITH SYSDATE NEXT SYSDATE+20/ (24*60) AS SELECT * FROM ABC WHERE TMSTP > SYSDATE-1;
Scenario:Intially data extracted will be "Greater than SYSDATE-1 = 18/08/2009 11:27:39 " - 76 Millions of Records After 20 Min of FAST Refresh, it will add 2500 Records
i)Is query in the MVIEWS (MV_ABC) SYSDATE-1 will be changed on each FAST Refresh, as observation (as per definition) it is incrementing the records to intial extracted data?
ii)As COMPLETE Refresh will be expensive to execute on the partition base table on each refresh. Is there any alternative way to achieve the Nature of COMPLETE REFRESH in Materialized View?
iii)Is Delete or Conditional Delete option is possible in Materialized View (i.e not a delete from the base table but the delete from MVIEWS)?
iv)What is the machnism for populating the logs for MVIEWS?Is it overhead for the system and better than a Trigger based approach?
how to check the time taken for materialized view to execute at one time.
My materialized view is a complete refresh which automatically executes at 3:00.
a) I have queried dba_jobs and dba_mview_refresh_times, and matched the last_date and last_refresh to find the total time. How can I be know that JOB 80 corresponds to my view MVIEW_UNION in a much easier way?
SQL> select job,last_date,total_time from dba_jobs order by last_date; JOB LAST_DATE TOTAL_TIME ---------- ----------------- ---------- 80 05/05/10 03:00:02 5255
SQL>select name,last_refresh from dba_mview_refresh_times where owner NAME LAST_REFRESH ------------------------------ ----------------- MVIEW_UNION 05/05/10 03:00:02
b) Also is total_time the time it took the query to execute today at 3:00, i.e it started at 3:00 and ended at around 4:45(3+5255 msec) or is it the overall time it has taken since the day it was scheduled in dba_jobs( i.e 1 week back)
c) Also my source table dosent contain any indexes and I have created indexes on materialized view, i want to know if the complete refresh will recreate the indxes.
CREATE MATERIALIZED VIEW LOG ON ABC; CREATE MATERIALIZED VIEW MV_ABC REFRESH FAST START WITH SYSDATE NEXT SYSDATE+20/(24*60) FOR UPDATE AS SELECT * FROM ABC WHERE TMSTP> SYSDATE-1;
It is working fine. Appended the Query with WHERE clause in MV_ABC MVIEWS.
DROP MATERIALIZED VIEW LOG ON ABC; DROP MATERIALIZED VIEW MV_ABC; CREATE MATERIALIZED VIEW LOG ON ABC; CREATE MATERIALIZED VIEW MV_ABC REFRESH FAST START WITH SYSDATE NEXT SYSDATE+20/(24*60) FOR UPDATE AS SELECT * FROM ABC WHERE TMSTP> SYSDATE-1;
ORA-12013: updatable materialized VIEWS must be simple enough TO DO fast refresh
I am having two server A and B.B is the replica of A.I have to alter the materialized view of one of the table in server B,of which Master table is in both the servers .I do not have any Materialized view log on both the servers and want to convert the Refresh method from Complete to Fast refresh.One way of this altration is to create materialized view log on master table.
i have created a fast refresh materialized view but it is not performing fast refresh or even complete refresh.Then i have checked my materialized view capabilities then in mv_capabilities_table i got a message that materialized view log is newer than old .
I'm trying to build up a materialized view with aggregate and FAST REFRESH for INSERT and UPDATE, DELETE with no success. But the web doesn't deny it ?
I have the source tables having 10 columns. I want to track the delta changes of only 6 columns. In case of insertion/updation, that new record should be inserted into another DELTA table of different schema.While in case of deletion, A new row will be inserted into that DELTA table , with the value of the primary key and other columns populated with NULL values.
I have applied the concept of 'after trigger' specifying the columns names but it logic is not working properly. Its tracking the changes for other 4 columns also.
We have a MV which fetches data from around 27 tables containing 26 joins out of which 25 are outer joins. Some tables in the query are being referred multiple times through different alias names and hence the actual no of physical tables used is 18. This MV takes about 50 mins to refresh through complete refresh mechanism. We decided to make it fast refresh and thus made these configurations:
- Created MV logs based on rowid for each of the base tables. - Recreated MV using FAST refresh,with primary key option enabled - Pulled rowid for all these tables in the select column statement.
Even after making all the recommendations suggested by Oracle for fast refresh MV's we are still getting refresh time of around 65 mins(refresh time increased!!!).We already have indexes built on all the join columns of the base tables. What else do we need to do to make this a "fast" refresh MV ?
1) TXN_HD_FCT (This table has 6 billion records. Has fields TXN_DT, CUST_ID, TXN_AMT, TXN_ID and the table is partitioned on TXN_DT. We load data approximately 600K+ records to this table every day 2) RELINK_CUST (This table has 2 fieds CUST_ID and NEW_CUST_ID). We load data approximatley 100-200K+ records every day into this table. This table has total 10MM+ records now.
Now i need to create a table (TXN_UPD_HDR_FCT) in Schema B by joining above 2 tables using below query every day
CREATE TABLE TXN_UPD_HDR_FCT select a.TXN_DT, nvl(b.new_cust_id, a.cust_id) as cust_id, a.txn_amt, a.txn_id, from schema_A.TXN_HD_FCT a, schema_A.RELINK_CUST b where a.cust_id = b.cust_id(+);
We update CUST_ID to NEW_CUST_ID based on some rules (and keep it in the table RELINK_CUST) and need to take this new_cust_id while building the table TXN_UPD_HDR_FCT.
Building this table is taking approximately 4+ hours every day. just refresh only records from TXN_HDR_FCT whose CUST_ID is changed or any records inserted into TXN_HDR_FCT? I read about Materialized views but not sure how can i create MV logs on 2 tables? If i use materialized views do i need to create indexes every day?
We are planning to create a materialized view on 5 tables and 2 views. After first complete refresh we want to do Fast refresh going forward and planning to create view log for base tables. Since the SQL uses two views, we can create view-log for its underlying tables. But will that work ? In case of fast refresh ? or it will still go for complete refresh ?
The customer moved their database from 10g to a 11gR2 database.They created the MV in the new system and now it takes 26 hours to refresh as opposed to 15 min in the old 10g database.Just looking for a game plan for troubleshooting.
I have a view that includes sub queries in its select.
I am creating a MV over that view. and i want to fast refresh it when ever a commit is placed on the table (that is used in the from clause for the simple view)
create materialized view ino_crm.MV_IDENTIFICATION_DETAILS REFRESH FAST as SELECT billing_account_id, i.s1 KEYS_ , COALESCE(i.s2, i.b, i.d) VALUES_ FROM ba_personal_details pd , xmltable ( '//entry[string = "identification Details"]//entry' passing xmltype(pd.details) columns s1 VARCHAR2 (255) path 'string[1]' ,s2 VARCHAR2(255) path 'string[2]' ,b VARCHAR2(255) path 'boolean' ,d VARCHAR2(255) path 'date' ) i WHERE pd.mandator_key = 'VSO01' AND pd.valid_to = to_date('01.01.4000', 'DD.MM.YYYY'); and
I get an exception like: SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query 12015. 00000 - "cannot create a fast refresh materialized view from a complex query"
*Cause: Neither ROWIDs and nor primary key constraints are supported for complex queries. *Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE option or create a simple materialized view.
I am not able to create materialized view with 'Refresh Fast on Commit' when the table is residing on the another user environment, I am not able to find the reason why?
You can re-generate the scenario at your end with the following code-
--Login to System- create user manu identified by manu; grant connect, resource to manu; create user arpit identified by arpit; grant connect, resource to arpit; grant create materialized view to manu;
[code]....
but when I am doing it with on demand options, materialized view is being created-
--Login to Manu- create materialized view arpit_view build immediate refresh on demand as select * from arpit.arpit /
I have a base table with ~20 mio. records with two FAST REFRESH Materialized Views based on that table using various aggregate functions in their view definition.
The problem is, when e.g. one record changes in the base table, I see two records in the MV log table MLOG$, but invoking the fast refresh mechanism by using using:
I'm trying to create materialized view, successfully create logs for all the tables involved.
CREATE MATERIALIZED VIEW LOG ON tbJournal WITH ROWID, SEQUENCE (tabid, companyid, storelocid, tabstatus, linetype) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON TBTABS WITH ROWID, SEQUENCE (TSTAMP, COMPANYID, NUMGUESTS, POSITIONID, STORELOCID, TABSSTATUS) INCLUDING NEW VALUES;
[code]....
The result is ora-12015: cannot create a fast refresh materialized view from a complex query
It does work fine if I remove
AND EXISTS (SELECT 1 FROM tbJournal J WHERE j.tabstatus NOT BETWEEN -6 AND -4 AND j.linetype = 1 AND T.tabid = J.tabid AND T.companyid = J.companyid AND T.storelocid = J.storelocid) from the where clause
During the MV refresh we are getting the following error and not able to fix it .
SQL> EXECUTE DBMS_MVIEW.REFRESH('EMP','F'); BEGIN DBMS_MVIEW.REFRESH('EMP','F'); END;
* ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-01400: cannot insert NULL into ("XYZ"."EMP"."DESCRIPTION") ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2426 ORA-06512: at line 1
The above column:- Emp.Description is a 'CLOB NOT NULL' .
We have created the MV using the below script
create materialized view EMP refresh on demand for update as select *from ABC.EMP@DBLINK;
I have attempted to set up the materialized view below to refresh complete at the time specified below. The problem is that it does not refresh and pick up the changes to one of the base tables.
Have some issue with Materialized view refresh. Have presented scenario which we have .
Schema1 : 1. Have base tables 2. Created MV logs on base tables
Schema2 : 1. Created sysnonyms for Schema1 base tables 2. created Materialized views 3. Created MV group , added views to the group
Executing the below command in schema2 : execute dbms_refresh.refresh('MV_grp');
Error : ORA-12048: error encountered while refreshing materialized view "Schema2"."Table1_MV" ORA-12018: following error encountered during code generation for "Schema2"."Table1_MV "ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461 ORA-06512: at "SYS.DBMS_IREFRESH", line 683 ORA-06512: at "SYS.DBMS_REFRESH", line 195 ORA-06512: at line 1
How can i use refresh option as 1 hr in creation of materialized view, Can i use unoin all in the materialized view and other clause like CONNECT BY PRIOR .
Materialized views are normally used for summarized data access.
CREATE MATERIALIZED VIEW mv_snapshot_A REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 20/1440 WITH PRIMARY KEY AS SELECT * FROM A;
This does not seem to be the case here as the materialized view seems to be just a full select. The overhead of the snapshot logs are concerning for this core table. Can we turn off logging in 10g ? the materialized view is defined as fast refresh/ build immediate .
The main requirement here is to keep the snapshot every 15 minutes so that the users can see the updated information ( the flow of data from one location to other).
User get the location wise count of data and can go further in details like in which location wise system wise data count. As the base table is volatile the materialized view is used so that the moment the user clicks for location wise details the data is static for 15 min and user don't get confused.
However, I need to refresh the group manually sometimes. Therefore, I cannot set the interval as sysdate+1.have tried setting the interval as follows. However, they are not correct
Quote:trunc(sysdate+1) +4/24 The next interval will show 9:25:20pm.
I created materialized view in Database B. The master table is in Database A. If i shutdown the database B and bring it up again, the next refresh time in dba_jobs tablein database A is coming as 01-jan-00. It is not getting refreshed automatically. The job_queue_process is 10. wat could be the reason, for the refresh failure.
I am working on materialized view replication but i have an crucial issue. I have a refresh group which contain many MVs some are Read only and some are update but all are in Fast refresh mode (logs are created on both replication and MV sites on these objects) and are referentially integrated to each other i.e they have parent child relationship with each other as at replication site on base tables.
When refresh group job runs to refresh these MVs i got the errors
ORA-02292: integrity constraint (string.string) violated - child record found
After that job status has changed to break,but in the same time all the pending transactions at the MVs sites are pushed to Replication site i e ( Base tables at replication site and MVs of these tables are synchronized).In order to change the status of job to Normal, i have to disable some constraints(not all) on the MVs and manually run the job .After that job status will become Normal even i enable these constraints with validation.
What will be the reason ? Are there some MVs trying to complete refresh instead of Fast or increment refresh in this refresh group?If yes how i can trace out MVs and solve this problem?
Some other information , Replication site running with 10gR2 Enterprise Edition. Materialized View site running with 10gR2 Slandered Edition.
