Replication :: Error Performing Fast Refresh Of Materialized View?
Sep 4, 2008
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.
View 4 Replies
ADVERTISEMENT
Jun 19, 2008
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.
View 4 Replies
View Related
Jun 20, 2011
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 .
View 2 Replies
View Related
Aug 20, 2009
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
View 6 Replies
View Related
Feb 8, 2012
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 ?
--build up needed schema objects
CREATE TABLE empt
( ename VARCHAR2(20),
empno INTEGER,
[Code]....
View 9 Replies
View Related
Oct 10, 2013
I've 2 tables in schema A.
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?
View 15 Replies
View Related
Jun 17, 2010
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 ?
View 9 Replies
View Related
Nov 1, 2012
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.
View 8 Replies
View Related
Mar 6, 2013
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)
View 9 Replies
View Related
Sep 26, 2013
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 have found this URL....
View 2 Replies
View Related
Jan 16, 2012
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:
dbms_snapshot.refresh('mv1', 'f');
dbms_snapshot.refresh('mv2', 'f');
Is still running after ~20 minutes now.
View 2 Replies
View Related
Apr 27, 2011
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
View 10 Replies
View Related
Jun 28, 2009
I created a Materialized view using joining of two tables with FAST REFRESH ON COMMIT option. These tables are the main tables of the DB and contains alot of data(about 15 millions of records). two master tables have index but mview doesn't have any indexes. I've thought the entire concept of the fast refresh is that it should be a relatively quick operation but the result showed that DB performance reduced, for example I could insert 900 records per minute thru application into master tables before but now I can just insert about 400 records per hour!.Also, It slows down the select operation, went from 0.11 seconds(with joining two tables) to 33 seconds (in mview)!.
First of all, I considered following possibilities and test them:
1) The most likely solution was that a complete refresh was happening. However, the materialized view refresh was confirmed to be a fast refresh by querying USER_MVIEWS.
2) I checked the query of the materialized view and confirmed that it was a simple select from the master tables without any aggregation or sorting.
3) DBMS_MVIEW.explain_mview was run on the materialized view. MV_CAPABILITIES_TABLE did not reveal any problems.
4) A query of dba_registered_snapshots showed that no other snapshots appeared to be using these logs. So, After each refresh, the records in mview logs were deleting.
As you see, it seems everything is ok, but what was causing this fast refresh to go so slow?
I use oracle 10.2 on Suse Linux enterprise server.
View 3 Replies
View Related
Jan 4, 2008
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.
View 6 Replies
View Related
Sep 12, 2008
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.
When I issue the command:
exec DBMS_REFRESH.REFRESH('TEAMSITE3.FIRMSEARCH_COWS_MV ');
the refresh works..
why this view is not refreshing at the time stated??
CREATE MATERIALIZED VIEW FIRMSEARCH_COWS_MV
REFRESH COMPLETE START WITH to_date('11-09-2008 10:00:00','dd-MM-yyyy hh24:mi:ss') NEXT (SYSDATE+1)
AS SELECT distinct spar.COW_REF
FROM LV_RECOGNISED_ATTAINMENTS lra,
LV_POSITION_IN_ORGANISATIONS rio,
[code]....
View 15 Replies
View Related
Nov 22, 2007
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
View 5 Replies
View Related
Dec 22, 2005
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 .
View 22 Replies
View Related
Apr 17, 2008
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.
View 5 Replies
View Related
Jan 6, 2010
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.
View 1 Replies
View Related
Jul 7, 2010
Create Materialized view with Refresh on commit example. with create Log file example.
View 6 Replies
View Related
May 5, 2010
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.
View 9 Replies
View Related
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.
how to again make the MVs fast refreshable?
View 4 Replies
View Related
Feb 7, 2012
I have created a Table with one column in user 'Test' Database 'Test'. Then i have created a Materialized view based on the Table in 'TestMV' in same database 'Test'.working fine. But after adding one column in the table, if i go to refresh the Materialized view the following Error has occured.
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00913: too many values
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 2
View 10 Replies
View Related
Apr 3, 2013
One table is partitioned. Below are my steps.
********************************************************************************************************
EXECUTE Dbms_Redefinition.can_redef_table('USRTEST2','ASH_PART_EMP');
CREATE TABLE USRTEST2.ASH_PART_EMP_PART
(
EMPNO NUMBER,
NAME VARCHAR2(30 BYTE),
[code].....
>> just after this command, below error occurred.
*****************************************************************************
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2
********************************************************************************
I am unable to UNDERSTAND, why this error is appearing? I have created new tablespace, new user, new table, then, from where this MATERIALIZED view error is coming?
View 2 Replies
View Related
Dec 8, 2011
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.
View 4 Replies
View Related
Jul 7, 2010
I have to convert some existing materialized views (fast refresh) to partition materialized views.
Database version is oracle 10.1.0.4. I have decided to use on prebuilt table option to do the partitioning as it minimizes the time to transfer from the master site.
1) stop replication
1) create interim tables with similar structure as the materialized views
2) transfer all data from the materialized views to the interim tables
4) script out the materialized views structure and add in on prebuilt table option in the scripts
5) drop the materialized views
6) rename the interim tables with the same name as the materialized views
7) run the scripts to create the materialized views with on prebuilt table option
8) refresh the newly created materialized views -> it should take a short time since I am using on prebuilt table option
But I am facing one major issue. That is if I drop the materialized views, the materialized view logs of the master tables are purged. When the materialized views are refreshed fast, there are some data missing. the data that are purged out when the materialized view are dropped.
Do you happen to know other ways that existing materialized views can be converted to partitioned materialized views? Do you have any workaround to prevent the materialized view logs from being purged?
View 3 Replies
View Related
Nov 19, 2010
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
/
View 5 Replies
View Related
Jul 21, 2009
I am having problems with my procedure which is refreshing materialized views. This is the error i am getting : -12008: ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number 14...
I have two database with the same procedure and the same objects. They run at the same time, however, there are times when i get the error on the other database while the other database runs the procedure without any error.
When i checked the net, they say that this error is caused by data that is old, so the solution is to make the source a predecessor of my procedure/job. But what i am thinking is that how was it possible for the other database to run it completely even if it is not yet linked as the successor of the source?
View 8 Replies
View Related
Sep 4, 2009
i need to set up a central server with all the master tables and two other local database which will hold the updatable materialized view of the master table...the databases must be synchronized with central server..and user will work on the materialized view database...
View 10 Replies
View Related
Mar 24, 2010
How to refresh the dependent materialized views in a snapshot group. It refreshes in the order we add the materialized views to snapshot group?
Here below is the scenario.
MATVW_A
CREATE MATERIALIZED VIEW MATVW_A BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM EMP;
MATVW_B
CREATE MATERIALIZED VIEW MATVW_B BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM MATVW_A;
MATVW_C
CREATE MATERIALIZED VIEW MATVW_C BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM MATVW_B;
I created a snapshotgroup SNGRP with all the above materialized views.
DBMS_REFRESH.MAKE(name => 'SNGRP',
tab => 'MATVW_A,MATVW_B,MATVW_C',
next_date => TRUNC(sysdate) + 1,
interval => 'SYSDATE + 1');
The order of refreshing the materialized views would be like first materialized view MATVW_A should get refresh then after MATVW_B,MATVW_C.
As they are dependent views.Is there anyway where we can mention the order of refreshing materialized views in snapshot group.
View 2 Replies
View Related
