Using MV Log for Incremental Refresh
A reader, April 05, 2003 - 3:10 pm UTC
I was thinking of using MV log to do incremental refresh. Say that I have a base_table and a refresh_table, which gets its data from base_table. I'll create a MV log on base_table so that all the DML operations on the table are captured. A scheduled job will process these DML operations to perform incremental refresh on refresh_table. Can this be done? Is this even a good approach?
thanks
April 05, 2003 - 3:19 pm UTC
are you saying you would refresh the table? why -- why wouldn't you just let it refresh all by itself?
Follow Up on Using MV Log for Incremental Refresh
A reader, April 05, 2003 - 3:32 pm UTC
Only a subset of refresh_table comes from base_table. base_table is only one of the sources.
April 05, 2003 - 3:42 pm UTC
well, you'll be hard pressed to successfully use our snapshot logs. it'll be tricky (and most importantly unsupported).
What you would need to do is:
update snaptime$$ to a constant -- it'll be 01-jan-4000, you could set it to SYSDATE.
Using that same SYSDATE, you can pull the primary keys of the modified rows and apply your changes.
Then, you can remove the rows you processed.
Otherwise, new rows will be added into the log as you are processing and you'll get "doubles" if you are not careful
Follow Up on Using MV Log for Incremental Refresh
A reader, April 05, 2003 - 3:51 pm UTC
So I'd update snaptime$$ to, say, SYSDATE to "freeze" the log. However, let's say if the same record has been inserted, updated, and updated again, how do I make sure that they are processed in that particular order since snaptime$$ for the three records recorded in the log would have the same time. Also in this case, don't I just need to process the last update statement by inserting a new record in refresh_table. Can this be done with snaptime$$?
April 05, 2003 - 4:25 pm UTC
oh, one you "freeze" the records -- then it is easy to refresh.
Just
delete from copy where pk in (select pk from log where dml_type in ('U','D') and snaptime$$= whatever);
insert into copy select * from base where pk in (select pk from log where dml_type in ('I','U') and snaptime$$ = whatever )
just get rid of all records that were modified or deleted and then add all that where modified or inserted. don't do anything in any order -- it wouldn't do anything for you...
drop materialized view log
shyampaliyath, April 29, 2003 - 12:53 am UTC
1)how to drop a materialized view log?
2)when i create a materialized view log it had created two tables MLOG$_EMP and RUPD$_EMP. what is the use of the RUPD$_EMP?
Materialized View log
shyampaliyath, April 29, 2003 - 12:57 am UTC
also what is the value of CHANGE_VECTOR$$ ??
MV Log
Rob, April 29, 2003 - 11:51 am UTC
Tom:
I am trying to use an MV log to propagate deltas in a work table to a dw table. The work table has a mix of regular dml and direct load (insert append). In my initial tests it seems that the data from the direct path load is not being transferred to the dw table when I do a fast refresh. Is fast refresh supported with direct load (the docs indicate that it is), can I mix direct load with conventional dml and still fast refresh. Anything different in the create log or create MV that I should be aware of to support the mix of direct load and standard dml.
Rob
April 29, 2003 - 12:04 pm UTC
review
Shyampaliyath, May 16, 2003 - 1:50 am UTC
this materialized view log keeps track of the DML operation done to the parent table . But how to keep track of the DML operation performed in the child tables??
May 16, 2003 - 9:53 am UTC
what child table??
each table would have its own log.
Materialized view log
A reader, June 16, 2004 - 11:13 pm UTC
Tom,
I am using Oracle9i and creating a materialized view log for base table for fast refresh. I notieced Oracle have log table named "MLOG$_XXX" for the log. My question is: will the log records stay there forever - do we need to do some cleaning work for the log table? Will Oracle automatically purge the log table when a success refresh is done?
Thanks as always.
June 17, 2004 - 9:26 am UTC
it'll clean up all by itself.
MV Logs for history for 9.2
A reader, June 23, 2004 - 6:16 pm UTC
Hello Sir,
1) Can I use MV logs to maintain a history
2) How to reconstruct the records thru a veiw on MVlog to go say for example in the past.Suppose I want to see all the records prior to the most current DML activity.
I see there is no last modified column in mv logs and just the snaptime$$ which is used only for refresh of mvs but i donot have any mvs. its just the log i use for history so i do not have to write triggers on an archive table.
update emp set ename = LOWER(ename) where empno = 7839 ;
update emp set ename = initcap(ename) where empno = 7839 ;
SELECT * FROM MLOG$_EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO M_ROW$$ SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
7839 KING PRESIDENT 11/17/1981 5000 10 AAAb8YAAVAAAWhqAAA 1/1/4000 U O 0400
7839 king PRESIDENT 11/17/1981 5000 10 AAAb8YAAVAAAWhqAAA 1/1/4000 U O 0400
I need to be able to go back in time.( I do not want to use Flash back)
CREATE TABLE EMP
(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL
)
LOGGING
NOCACHE
NOPARALLEL;
CREATE UNIQUE INDEX EMP_PRIMARY_KEY ON EMP
(EMPNO)
LOGGING
NOPARALLEL;
ALTER TABLE EMP ADD (
CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7839, 'KING', 'PRESIDENT', NULL, TO_Date( '11/17/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 5000, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7698, 'BLAKE', 'MANAGER', 7839, TO_Date( '05/01/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 2850, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7782, 'CLARK', 'MANAGER', 7839, TO_Date( '06/09/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 2450, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7566, 'JONES', 'MANAGER', 7839, TO_Date( '04/02/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 2975, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7654, 'MARTIN', 'SALESMAN', 7698, TO_Date( '09/28/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1250, 1400, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7499, 'ALLEN', 'SALESMAN', 7698, TO_Date( '02/20/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1600, 300, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7844, 'TURNER', 'SALESMAN', 7698, TO_Date( '09/08/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1500, 0, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7900, 'JAMES', 'CLERK', 7698, TO_Date( '12/03/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 950, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7521, 'WARD', 'SALESMAN', 7698, TO_Date( '02/22/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1250, 500, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7902, 'FORD', 'ANALYST', 7566, TO_Date( '12/03/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7369, 'SMITH', 'CLERK', 7902, TO_Date( '12/17/1980 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 800, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7788, 'SCOTT', 'ANALYST', 7566, TO_Date( '12/09/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7876, 'ADAMS', 'CLERK', 7788, TO_Date( '01/12/1983 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1100, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7934, 'MILLER', 'CLERK', 7782, TO_Date( '01/23/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1300, NULL, 10);
COMMIT;
CREATE MATERIALIZED VIEW LOG ON emp WITH( ename, job, mgr, hiredate, sal, comm, deptno) ,ROWID
Trigger on Materialized view is fired only once !!
Rohit, February 18, 2005 - 1:39 am UTC
Hello Tom,
I have created a Materialized view to replicate data from
details table. The requirement is to allow INSERT and UPDATE on MV but to restrict the DELETION of records (or somehow keep the deleted record in MV).
For this I have attached a AFTER ON DELETE trigger on the MV.The trigger is reinserting the deleted record back in the MV. The problem is, the trigger is getting fired only once. That is after first deletion from Master table, the AFTER ON DELETE trigger on MV is getting fired and the record is getting inserted properly.
From next time if a record is deleted from Master, the trigger on MV doesnot fire and the record gets deleted from MV also!! Is the replication takes some other internal table for updating the MV ?
The table and MV definition is:
create table TestTable
(
ID varchar2(10) PRIMARY KEY,
Value varchar2(100)
);
CREATE MATERIALIZED VIEW LOG ON TestTable WITH ROWID;
Create Snapshot Local_TestTable
storage (initial 100k next 100k pctincrease 10)
refresh fast
start with sysdate next SYSDATE + 1/(24 * 240)
WITH ROWID
ENABLE QUERY REWRITE
as
Select * from Testtable;
---The trigger----
--The Table is TestTable
-- The MV is Local_TestTable
----
Create or Replace Package pkg_HandleDel
AS
Type RowArray is Table of Local_TestTable%rowtype Index by Binary_Integer;
oldVal RowArray;
emptyArr RowArray;
end;
/
Create Or Replace Trigger trg_Stmt_BeforeDelete
Before Delete on Local_TestTable
Begin
pkg_HandleDel.oldVal:= pkg_HandleDel.emptyArr;
End;
/
Create Or Replace Trigger trg_Row_BeforeDelete
Before Delete on Local_TestTable
For Each Row
Declare
i Number Default pkg_HandleDel.oldVal.count+1;
Begin
pkg_HandleDel.oldVal(i).Id:=:old.Id;
pkg_HandleDel.oldVal(i).Value:=:old.Value;
End;
/
Create Or Replace Trigger trg_Row_AfterDelete
After Delete on Local_TestTable
Declare
ERR_MSG VARCHAR(400);
ERR_CDE NUMBER;
Begin
DBMS_OUTPUT.PUT_LINE('Inside Trigger');
For i in 1..pkg_HandleDel.oldVal.count Loop
Insert Into Local_TestTable Values
(
pkg_HandleDel.oldVal(i).Id,
pkg_HandleDel.oldVal(i).Value
);
End Loop;
Exception
When OTHERS then
ERR_MSG := SUBSTR(SQLERRM,1,100);
ERR_CDE := SQLCODE;
DBMS_OUTPUT.PUT_LINE(NVL(SUBSTR((ERR_CDE ||' - ' ||ERR_MSG),1,200),'-'));
End;
/
------Trigger Ends-----
The Database version is Oracle 8.1.7
query_rewrite_enabled is TRUE
query_rewrite_integrity= STALE_TOLERATED
Please suggest..
Thanks in advance
Rohit
February 18, 2005 - 8:02 am UTC
your requirements as they are do not fit in with what MV's do.
won't happen with a MV. Abandon this path -- not going to work.
MV or not MV
Nurlan, January 02, 2006 - 5:22 am UTC
Hello Tom,
I am facing the same problem as the reader above, who is going to use in house developed routine to refresh MV, namely using snapshot logs to fast refresh tables. In my case fast refresh did not work because we have to use PL/SQL procedures (complex) to obtain values (98% of recursive calls). The application is written this way and we can not change it. As a result, a full refresh of table with 200K rows is taking forever.
I was thinking about prebuilt table option for MV with unmanaged columns and update those columns later.
But I am wondering, if it is the proper way of doing it. Does unmanaged MVs really work? Could you refer me to an example?
Thank you a lot
January 02, 2006 - 9:59 am UTC
I don't know what you mean by "unmanaged columns".
Follow up
Nurlan, January 04, 2006 - 11:36 pm UTC
This is from Oracle:
The table involved in a materialized view can contain columns that are not
referenced in the defining query of the materialized view(unmanaged columns).
If rows are inserted during a refresh operation, each unmnanaged column of the
row is set to its default value, therefore the unmanaged columns cannot have
NOT NULL constraints unless they also have default values.
Unmanaged columns are not supported by single table aggregate materialized
views or materialized views containing joins only.
January 05, 2006 - 10:24 am UTC
This is what I read:
...
The requirement is to allow INSERT and UPDATE on MV but to
restrict the DELETION of records (or somehow keep the deleted record in MV).
......
I stopped reading at that point, it was not useful to read further. My response:
...
your requirements as they are do not fit in with what MV's do.
won't happen with a MV. Abandon this path -- not going to work.
.......
stands as is.
Change Capture in a Data Warehouse
Su Baba, February 12, 2006 - 9:07 pm UTC
Would I be able to use the materialized view logs the way they were described in the original question/answer to implement change capture in a data warehouse.
Let say the requirement is to populate a staging table from multiple data sources. Would I be able to employ the logic mentioned above to successfully capture the changes?
Would this be your recommended approach in a situation where the data volume is huge?
February 13, 2006 - 8:10 am UTC
*We* can use materialized view logs to propogate changes to some other system, sure.
*You* can create mv logs to help us do that.
the original question/answer didn't seem to be describing anything about implementing anything. It was a simple question about the snaptime$$ column.
You would never use the MV logs directly, you would have them so WE can use them.
There is
o streams
o CDC (change data capture)
both documented - for the "do it yourselfers"
Snaptime$$ remains 4000 after fast refresh
Vladimir Nicolici, January 22, 2007 - 9:18 am UTC
I have a large table, about 1GB in size, replicated to 3 destinations, using materialized view replication.
The source table has a materialized view log based on PK, and the destinations are performing fast refresh every 60 seconds.
About every 10 days, after running some large batches that modify the source table, the users start to complain that the replication is not working.
When I check mlog$_table_name, I find the entries for the modified records, and Snaptime$$ is 4000, remains 4000, and the changes are not replicated.
But if I look in the dba_snapshot_logs table on the source database, the timestamp for each destination changes every 60 seconds, as it should.
If I perform a fast refresh manually on the destinations, the refreshes complete "successfully", but Snaptime$$ remains 4000, and the problem persists, the changed rows are not replicated.
No errors are logged, and the only way to solve the problem, is to perform a complete refresh on the table following night.
This is very annoying, considering I prefer to do other things at night rather than shutdown applications/perform complete refresh/start applications.
The source database version is 8.1.7.4, and the destination databases are 2 x 9.2.0.4 and one 10.2.0.1 .
Snaptime$$ remains 4000 after fast refresh
Vladimir Nicolici, February 02, 2007 - 3:13 am UTC
It just happened again, to a different table. This time I was able to fix the problem by stopping/starting the master replication group.
Multiple snapshots
Sri, April 27, 2007 - 6:17 am UTC
Hi Tom,
You said "Since S2 is the last snapshot in need of refresh from this record -- it is removed from the log"
1. How does Oracle know whether S2 is the last snapshot "in need of" this particular log or not? Can you please let me know through which data dictionary view the information regarding which all snapshots are feeding from a particular log can be seen?
2. How can I change this dependency? I mean, if I no longer want the log to be kept for S2, what should I do? (I don't want to drop S2, but I am sure that I don't need to refresh any more)
3. If I drop the snapshot S2, will Oracle remove the dependency? What if the snapshot S2 is in a remote site.
Thanks.
MV Log Name and Required grants
Rick Davis, May 10, 2007 - 8:21 am UTC
Hi Tom and thank you for the tremendous service you provide the Oracle community. I searched the manuals, your books, and this site and can't find a reference for the following questions. I can see that Oracle indeed prefaces MLOB$_ when creating the MV log name, however, in 10Gr2, and I assume below, it appears to have a max of 26 bytes? It also appears that the grants on the master table must also be granted on the MV log? Regards ....
May 11, 2007 - 10:51 am UTC
the mv log is an internal device, you would not really poke around with it (hence why care what it is named?) What are you asking?
the table name can be 30 characters
we'll name the logs as we like:
ops$tkyte%ORA10GR2> create table abcdefghijklmnopqrstuvwxyz1234 ( x int primary key, y int );
Table created.
ops$tkyte%ORA10GR2> create table abcdefghijklmnopqrstuvwxyz1235 ( x int primary key, y int );
Table created.
ops$tkyte%ORA10GR2> create materialized view log on abcdefghijklmnopqrstuvwxyz1234;
Materialized view log created.
ops$tkyte%ORA10GR2> create materialized view log on abcdefghijklmnopqrstuvwxyz1235;
Materialized view log created.
ops$tkyte%ORA10GR2> @dbls
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX SYS_C0015267 USERS
SYS_C0015268 USERS
TABLE ABCDEFGHIJKLMNOPQRSTUVWXYZ1234 USERS
ABCDEFGHIJKLMNOPQRSTUVWXYZ1235 USERS
MLOG$_ABCDEFGHIJKLMNOPQRST USERS
MLOG$_ABCDEFGHIJKLMNOPQRST1 USERS
RUPD$_ABCDEFGHIJKLMNOPQRST
RUPD$_ABCDEFGHIJKLMNOPQRST1
8 rows selected.
and as for the grants, you need to grant "nothing" - not sure what you are trying to do - so cannot say what would need be granted.
MV Log Issue
Rick Davis, May 11, 2007 - 11:46 am UTC
Thanks to you I think I figured out the issue.
We create the MV Log in one schema, but create the MV in a different schema. Thus, all I really need to do is grant select on the base table to the other schema; something like the following, right?
CREATE MATERIALIZED VIEW LOG ON CDW_DM.BULK_CHEM_SITE_DIM;
GRANT SELECT ON CDW_DM.BULK_CHEM_SITE_DIM TO CDW_DM_SHARE WITH GRANT OPTION;
CREATE MATERIALIZED VIEW CDW_DM_SHARE.BULK_CHEM_SITE_DIM_MV ...
MV Log Issue
A reader, May 11, 2007 - 12:19 pm UTC
Hi Tom, an interesting test follows. Note second attempt works after granting select on the MV Log:
SQL> select SYS_CONTEXT('USERENV', 'DB_NAME'), USER, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MM:SS pm') from dual;
SYS_CONTEXT('USERENV','DB_NAME')
--------------------------------------------------------------------------------
USER TO_CHAR(SYSDATE,'DD-MON
------------------------------ -----------------------
usmgend
CDW 11-MAY-2007 10:05:39 am
SQL>
SQL> DROP TABLE RICKTEST;
Table dropped.
SQL>
SQL> CREATE TABLE RICKTEST (COL1 NUMBER Primary key);
Table created.
SQL>
SQL> Insert into RICKTEST (COL1) VALUES (1);
1 row created.
SQL>
SQL> insert into RICKTEST (COL1) VALUES (2);
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> DROP MATERIALIZED VIEW LOG ON RICKTEST;
DROP MATERIALIZED VIEW LOG ON RICKTEST
*
ERROR at line 1:
ORA-12002: there is no materialized view log on table "CDW"."RICKTEST"
SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON RICKTEST;
Materialized view log created.
SQL>
SQL> GRANT SELECT ON RICKTEST TO CDW_DM WITH GRANT OPTION;
Grant succeeded.
SQL>
SQL> set echo off
SQL> connect cdw_dm@usmgend
Enter password: *****
Connected.
SQL> @testMV_CDW_DM.sql
SQL>
SQL> spool testMV_CDW_DM.log
SQL>
SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
CDW_DM
SQL>
SQL> select SYS_CONTEXT('USERENV', 'DB_NAME'), USER, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MM:SS pm') from dual;
SYS_CONTEXT('USERENV','DB_NAME')
--------------------------------------------------------------------------------
USER TO_CHAR(SYSDATE,'DD-MON
------------------------------ -----------------------
usmgend
CDW_DM 11-MAY-2007 10:05:21 am
SQL>
SQL> CREATE MATERIALIZED VIEW RICKTEST_MV
2 TABLESPACE CDW_DM_DATA01
3 NOCACHE
4 LOGGING
5 NOCOMPRESS
6 NOPARALLEL
7 BUILD IMMEDIATE
8 USING INDEX TABLESPACE CDW_DM_IDX01
9 REFRESH FAST ON DEMAND
10 WITH PRIMARY KEY
11 AS SELECT * FROM CDW.RICKTEST;
AS SELECT * FROM CDW.RICKTEST
*
ERROR at line 11:
ORA-12018: following error encountered during code generation for
"CDW_DM"."RICKTEST_MV"
ORA-01031: insufficient privileges
SQL>
SQL> set echo off
SQL> connect cdw@usmgend
Enter password: ******
Connected.
SQL> @testMV_CDW.sql
SQL>
SQL> spool testMV_CDW.log
SQL>
SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
CDW
SQL>
SQL> select SYS_CONTEXT('USERENV', 'DB_NAME'), USER, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MM:SS pm') from dual;
SYS_CONTEXT('USERENV','DB_NAME')
--------------------------------------------------------------------------------
USER TO_CHAR(SYSDATE,'DD-MON
------------------------------ -----------------------
usmgend
CDW 11-MAY-2007 11:05:59 am
SQL>
SQL> DROP TABLE RICKTEST;
Table dropped.
SQL>
SQL> CREATE TABLE RICKTEST (COL1 NUMBER Primary key);
Table created.
SQL>
SQL> Insert into RICKTEST (COL1) VALUES (1);
1 row created.
SQL>
SQL> insert into RICKTEST (COL1) VALUES (2);
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> DROP MATERIALIZED VIEW LOG ON RICKTEST;
DROP MATERIALIZED VIEW LOG ON RICKTEST
*
ERROR at line 1:
ORA-12002: there is no materialized view log on table "CDW"."RICKTEST"
SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON RICKTEST;
Materialized view log created.
SQL>
SQL> GRANT SELECT ON RICKTEST TO CDW_DM WITH GRANT OPTION;
Grant succeeded.
SQL>
SQL> GRANT SELECT ON MLOG$_RICKTEST TO CDW_DM WITH GRANT OPTION;
Grant succeeded.
SQL>
SQL> set echo off
SQL> connect cdw_dm@usmgend
Enter password: *****
Connected.
SQL> @testMV_CDW_DM.sql
SQL>
SQL> spool testMV_CDW_DM.log
SQL>
SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
CDW_DM
SQL>
SQL> select SYS_CONTEXT('USERENV', 'DB_NAME'), USER, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MM:SS pm') from dual;
SYS_CONTEXT('USERENV','DB_NAME')
--------------------------------------------------------------------------------
USER TO_CHAR(SYSDATE,'DD-MON
------------------------------ -----------------------
usmgend
CDW_DM 11-MAY-2007 11:05:41 am
SQL>
SQL> CREATE MATERIALIZED VIEW RICKTEST_MV
2 TABLESPACE CDW_DM_DATA01
3 NOCACHE
4 LOGGING
5 NOCOMPRESS
6 NOPARALLEL
7 BUILD IMMEDIATE
8 USING INDEX TABLESPACE CDW_DM_IDX01
9 REFRESH FAST ON DEMAND
10 WITH PRIMARY KEY
11 AS SELECT * FROM CDW.RICKTEST;
Materialized view created.
SQL>
May 11, 2007 - 1:34 pm UTC
can you summarize what we are looking at and what the question is.
MV Log Issue
A reader, May 11, 2007 - 4:42 pm UTC
Tom, thanks for your time and expertise. The basic problem is a failure attempting the build of the MV in a different schema than were the base table and MV Log exists with insufficient privledges. If you build the MV REFRESH COMPLETE a grant on the MV Log isn't required; however, build it REFRESH COMMIT or REFRESH FAST (which requires a read of the MV Log) you must grant select on the MV Log to the schema where the MV will reside. That's where the 26 bytes comes into play. It appears that Oracle prefixes on the MLOG$_ to the tablename and truncates off any bytes beyond 26 to name the Log. Thus the grant to or drop of the MV Log must match the 26 byte name created by Oracle.
Boy, I hope this makes sense.
What is purpose of SNAPTIME$$ & CHANGE_VECTOR$$
Supriya, October 15, 2007 - 3:34 am UTC
When I perform DML operations on Master table, MV log populates with data but SNAPTIME$$ always
contains '1/1/4000' date. Also after running it's job manually MV refreshes with new values but
when job runs automatically then MV is not refreshing...
What will be the problem?
I have using Snapshot replication remotely.
Please guide ... it's very urgent!
Thanks in advance
How does it know ?
Anil Pant, May 29, 2008 - 10:41 am UTC
Hello Tom,
Replying to the original question you said:
<<START>>
snaptime$$ is modified upon a refresh and is only used when there are multiple snapshots
defined on a single master.
When a snapshot refreshes -- it sets this column to the time it did its refresh. That
way, when it refreshes later -- it can skip rows it already did.
Say you have two snapshots S1 and S1. You have a master table M.
You have a snapshot log with:
1 01014000 00:00:00 I N
in it. Say it is now noon on April 4th 2003. S1 was last refreshed at 11am on the same
day. S2 was last refreshed at 9am that morning. S1 refreshes by pulling every change
from the snapshot log where snaptime$$ is greater then 11am April 4th 2003. That record
from the year 4000 is so it gets applied. When S1 is done, the snapshot log would have:
1 04042003 12:00:00 I N
Now at 1pm, S1 refreshes again. Nothing happens -- there are no newer records to
process. At 1:05pm, S2 refreshes. The record in there happened after 9am (S2's last
refresh) so it pulls it. Since S2 is the last snapshot in need of refresh from this
record -- it is removed from the log.
So, snaptime$$ is used to coordinate multiple snapshots using the same log.
<<END>>
But how it know there are multiple snapshots for the same log?
May 29, 2008 - 11:04 am UTC
dba_registered_* views show you.
ops$tkyte%ORA9IR2> create table t ( x int primary key, y int );
Table created.
ops$tkyte%ORA9IR2> create materialized view log on t with rowid (y) including new values;
Materialized view log created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create materialized view mv1 refresh fast
2 as select count(*) from t;
Materialized view created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create materialized view mv2 refresh fast
2 as select y, count(*) from t group by y;
Materialized view created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select * from user_registered_mviews;
OWNER NAME
------------------------------ ------------------------------
MVIEW_SITE
-------------------------------------------------------------------------------
CAN UPD REFRESH_MET MVIEW_ID VERSION
--- --- ----------- ---------- --------------------------
QUERY_TXT
-------------------------------------------------------------------------------
OPS$TKYTE MV1
ORA9IR2.US.ORACLE.COM
YES NO ROWID 171 ORACLE 8 MATERIALIZED VIEW
select count(*) from t
OPS$TKYTE MV2
ORA9IR2.US.ORACLE.COM
YES NO ROWID 172 ORACLE 8 MATERIALIZED VIEW
select y, count(*) from t group by y
How to get Last row updated by MV
Ankur, June 05, 2008 - 10:19 am UTC
Hi Tom,
I've a MV and a after row trigger is placed on it which is updating a table say a1. Now I want to insert only one row in another table say a2 through that trigger when MV is refreshing the last row, how can I came to know in trigger that MV is updating the last row so that Insert can be run.
Thanks In Advance
Ankur
June 05, 2008 - 10:31 am UTC
the only supported triggers on materialized views are
o on UPDATEABLE materialized views
o and they promise to NOT DO ANYTHING during a refresh
your trigger violates both of those..
You cannot know when we are refreshing the "last row", what you might do is this:
create procedure my_refresh
as
begin
dbms_mview.refresh( ... );
insert into a2 ... whatever ....
commit;
end;
/
and schedule THAT procedure to run on the same schedule as your MV is right now and disable the MV from refreshing itself.
That would seem to accomplish your goal.
Please Clarify
Ankur, June 05, 2008 - 10:47 am UTC
Thanks a lot Tom !
Can you please clarify this -->
<<
the only supported triggers on materialized views are
o on UPDATEABLE materialized views
o and they promise to NOT DO ANYTHING during a refresh
your trigger violates both of those..
>>
Thanks
Ankur
June 05, 2008 - 11:01 am UTC
what needs be clarified?
you may have a trigger on an updateable materialized view
and it must promise to just "return" when invoked as part of a refresh
meaning, you do not put triggers on read only materialized views
you cannot rely on any order of processing
you cannot assume that to update a row, we'll update a row (we could delete+insert if we feel like it)
you cannot assume even that your trigger will FIRE during a refresh.
so, do not put a trigger on a read only materialized view - since it would only be invoked during a refresh and during a refresh you have to promise to "not do anything" in the trigger.
MV log tables not getting cleared after FAST REFRESH
Megala, June 24, 2008 - 6:19 pm UTC
Tom:
Is it required to grant DELETE privilege also on MV log table to the read-only user used by remote materialized view site.
Master site MV log tables are not getting cleared even after FAST refresh done. So just wondering if DELETE privilege on MV log table needs to be granted to MVread-only used by materialized view site.
I could not reproduce this problem when i tested it on some test table. Without DELETE privilege on MV log table, my mv log table gets cleared after FAST refresh on target site.
I am confused on this behaviour.
June 24, 2008 - 7:46 pm UTC
look at the registered materialized views on the site with the logs. Odds are you have one registered that isn't refreshing and it is the root cause - we keep the records until the last one uses them, then we purge them.
user_registred_mviews count does not match _base_able_mviews
Megala, June 24, 2008 - 10:16 pm UTC
Tom:
Thanks.
I checked on master site (via dblink), rowcount does not match between all_registered_mviews and all_base_table_mviews. I noticed two different mview_ids for the same master table. But only one registered site.
How to find the target materialized view using master site MVIEW_ID ?
SQL> select count(*) from all_registered_mviews@nc3_mat_link ;
COUNT(*)
----------
133
SQL> select count(*) from all_base_table_mviews@nc3_mat_link ;
COUNT(*)
----------
249
SQL> select distinct MVIEW_SITE from all_Registered_mviews@NC3_MAT_LINK ;
MVIEW_SITE
------------------------------
DBPROD18.NOC.ATT.COM
SQL> @qry_remote_mvsite
SQL> column MVIEW_SITE format a30
SQL> select a.owner, a.master, to_char(a.MVIEW_LAST_REFRESH_TIME,'mm/dd/yy hh24:mi:ss')
2 MVIEW_LAST_REFRESH_TIME, b.MVIEW_SITE
3 --b.REFRESH_METHOD
4 from all_BASE_TABLE_MVIEWS@NC3_MAT_LINK a, all_Registered_mviews@NC3_MAT_LINK b
5 where a.MVIEW_ID=b.MVIEW_ID
6 --and ( b.MVIEW_SITE like '%DBDC01%' OR b.MVIEW_SITE like '%DBPROD23%' )
7 order by a.master, MVIEW_LAST_REFRESH_TIME
8 /
OWNER MASTER MVIEW_LAST_REFRES MVIEW_SITE
------------------------------ ------------------------------ ----------------- ------------------------------
NC3 AATMPVC 06/24/08 01:00:11 DBPROD18.NOC.ATT.COM
NC3 ACONFIG 06/24/08 01:00:11 DBPROD18.NOC.ATT.COM
NC3 AEQP 06/24/08 01:00:11 DBPROD18.NOC.ATT.COM
NC3 ALOCN 06/24/08 01:00:11 DBPROD18.NOC.ATT.COM
NC3 AMRPTST 06/24/08 01:00:11 DBPROD18.NOC.ATT.COM
NC3 ANX_EXT 06/24/08 01:00:14 DBPROD18.NOC.ATT.COM
NC3 APVC 06/24/08 01:00:14 DBPROD18.NOC.ATT.COM
NC3 ASITE 06/24/08 01:00:57 DBPROD18.NOC.ATT.COM
NC3 ASSET 06/24/08 01:02:21 DBPROD18.NOC.ATT.COM
NC3 ASSET_EQP 06/24/08 01:02:48 DBPROD18.NOC.ATT.COM
NC3 ATM_PVC 06/24/08 01:00:15 DBPROD18.NOC.ATT.COM
NC3 ATM_SIGNALLING 06/24/08 01:00:15 DBPROD18.NOC.ATT.COM
NC3 ATM_TRAFFIC 06/24/08 01:00:22 DBPROD18.NOC.ATT.COM
NC3 ATM_USER_PREF 06/24/08 01:00:22 DBPROD18.NOC.ATT.COM
NC3 AUTH 06/24/08 14:14:51 DBPROD18.NOC.ATT.COM
NC3 AUTH_TOKEN 06/24/08 01:00:30 DBPROD18.NOC.ATT.COM
NC3 BOX_PROF 06/24/08 01:00:32 DBPROD18.NOC.ATT.COM
NC3 CARRIED_CKT 06/24/08 01:00:32 DBPROD18.NOC.ATT.COM
NC3 CARRIED_CKT_ETV 06/24/08 01:00:32 DBPROD18.NOC.ATT.COM
NC3 CATALOG_ITEM 06/24/08 01:02:51 DBPROD18.NOC.ATT.COM
NC3 CDVR_QA_CFG 06/24/08 01:00:37 DBPROD18.NOC.ATT.COM
NC3 CHANGE_HISTORY 06/24/08 01:00:41 DBPROD18.NOC.ATT.COM
NC3 CHNNL 06/24/08 01:00:41 DBPROD18.NOC.ATT.COM
NC3 CHNNL_CARRIED_CKT 06/24/08 01:00:42 DBPROD18.NOC.ATT.COM
NC3 CIRCUIT_LOC 06/24/08 01:00:43 DBPROD18.NOC.ATT.COM
NC3 CIRCUIT_REF 06/24/08 01:00:43 DBPROD18.NOC.ATT.COM
NC3 CLIN 06/24/08 01:01:17 DBPROD18.NOC.ATT.COM
NC3 CMP_PARM_REF 06/24/08 01:00:43 DBPROD18.NOC.ATT.COM
NC3 CMP_PROFILE 06/24/08 01:00:43 DBPROD18.NOC.ATT.COM
NC3 CMP_PROFILE_CMP 06/24/08 01:00:47 DBPROD18.NOC.ATT.COM
NC3 CMP_PROFILE_USAGE 06/24/08 01:00:55 DBPROD18.NOC.ATT.COM
NC3 CMP_TO_CMP 06/24/08 01:00:57 DBPROD18.NOC.ATT.COM
NC3 CMP_USAGE 06/24/08 01:01:09 DBPROD18.NOC.ATT.COM
NC3 COMPONENT_COORD 06/24/08 01:01:21 DBPROD18.NOC.ATT.COM
NC3 COMPONENT_OBJ_REF 06/24/08 01:01:44 DBPROD18.NOC.ATT.COM
NC3 CONFIG 06/24/08 01:01:48 DBPROD18.NOC.ATT.COM
NC3 CONFIG_CHNNL_REF 06/24/08 01:01:54 DBPROD18.NOC.ATT.COM
NC3 CONFIG_REF 06/24/08 01:02:03 DBPROD18.NOC.ATT.COM
NC3 CONNECTION 06/24/08 01:02:53 DBPROD18.NOC.ATT.COM
NC3 CONNECTOR_COORD 06/24/08 01:02:38 DBPROD18.NOC.ATT.COM
NC3 CONN_REF 06/24/08 01:02:44 DBPROD18.NOC.ATT.COM
NC3 CONTAIN 06/24/08 01:02:55 DBPROD18.NOC.ATT.COM
NC3 CONT_RSRC 06/24/08 01:03:00 DBPROD18.NOC.ATT.COM
NC3 COS_CDR 06/24/08 01:03:00 DBPROD18.NOC.ATT.COM
NC3 COS_DATA 06/24/08 01:01:48 DBPROD18.NOC.ATT.COM
NC3 COS_PROFILE 06/24/08 01:01:59 DBPROD18.NOC.ATT.COM
NC3 COS_TRAFFIC_APPL 06/24/08 01:03:01 DBPROD18.NOC.ATT.COM
NC3 COS_TRAFFIC_IP 06/24/08 01:03:03 DBPROD18.NOC.ATT.COM
NC3 COS_TRAFFIC_IP_PREC 06/24/08 01:03:04 DBPROD18.NOC.ATT.COM
NC3 CUSTOM_QUEUE 06/24/08 01:03:11 DBPROD18.NOC.ATT.COM
NC3 DEFINED_PATH 06/24/08 01:03:11 DBPROD18.NOC.ATT.COM
NC3 EOL 06/24/08 01:02:54 DBPROD18.NOC.ATT.COM
NC3 EOL_LINE 06/24/08 01:02:55 DBPROD18.NOC.ATT.COM
NC3 EOL_LINE_ITEM 06/24/08 01:02:58 DBPROD18.NOC.ATT.COM
NC3 EQP 06/24/08 01:00:51 DBPROD18.NOC.ATT.COM
NC3 EQPORT_EQPORT_REF 06/24/08 01:03:13 DBPROD18.NOC.ATT.COM
NC3 EQP_LOC 06/24/08 01:00:10 DBPROD18.NOC.ATT.COM
NC3 EQP_OPTION 06/24/08 16:51:49 DBPROD18.NOC.ATT.COM
NC3 EQP_PORT_REF 06/24/08 01:00:11 DBPROD18.NOC.ATT.COM
NC3 EQP_REF 06/24/08 01:00:11 DBPROD18.NOC.ATT.COM
NC3 EQP_REF_DOC 06/24/08 01:00:11 DBPROD18.NOC.ATT.COM
NC3 EQP_SLOT_REF 06/24/08 01:00:11 DBPROD18.NOC.ATT.COM
NC3 FREEFORM_TEXT 06/24/08 01:00:22 DBPROD18.NOC.ATT.COM
NC3 ILMI 06/24/08 01:00:22 DBPROD18.NOC.ATT.COM
NC3 INFOCONN 06/24/08 01:00:22 DBPROD18.NOC.ATT.COM
NC3 INVENTORY_WORK_CENTER 06/24/08 01:03:09 DBPROD18.NOC.ATT.COM
NC3 IPX_TRADE_PARTNER 06/24/08 01:00:22 DBPROD18.NOC.ATT.COM
NC3 IP_HELPER 06/24/08 01:00:24 DBPROD18.NOC.ATT.COM
NC3 IP_INTERFACE 06/24/08 01:00:27 DBPROD18.NOC.ATT.COM
NC3 IP_TRADE_PARTNER 06/24/08 01:00:27 DBPROD18.NOC.ATT.COM
NC3 ISDN_CHANNEL 06/24/08 01:00:28 DBPROD18.NOC.ATT.COM
NC3 ISDN_LINE 06/24/08 01:00:30 DBPROD18.NOC.ATT.COM
NC3 LAN_SEGMENT 06/24/08 01:00:32 DBPROD18.NOC.ATT.COM
NC3 LOCATION 06/24/08 01:00:32 DBPROD18.NOC.ATT.COM
NC3 LOG_PORT 06/24/08 01:00:38 DBPROD18.NOC.ATT.COM
NC3 LOG_PORT_REF 06/24/08 01:00:39 DBPROD18.NOC.ATT.COM
NC3 MAINT_VENDOR_DATA 06/24/08 01:00:51 DBPROD18.NOC.ATT.COM
NC3 MISC_CMP 06/24/08 01:00:40 DBPROD18.NOC.ATT.COM
NC3 MISC_CMP_REF 06/24/08 01:00:40 DBPROD18.NOC.ATT.COM
NC3 MULT_CAST 06/24/08 01:00:40 DBPROD18.NOC.ATT.COM
NC3 MULT_MEM 06/24/08 01:00:43 DBPROD18.NOC.ATT.COM
NC3 NETBIOS_TRADE_PART 06/24/08 01:00:47 DBPROD18.NOC.ATT.COM
NC3 NEWODBID 06/24/08 01:00:47 DBPROD18.NOC.ATT.COM
NC3 ORDERS 06/24/08 01:00:47 DBPROD18.NOC.ATT.COM
NC3 ORDER_CONFIG 06/24/08 01:00:50 DBPROD18.NOC.ATT.COM
NC3 ORD_CMP_ORG 06/24/08 01:00:51 DBPROD18.NOC.ATT.COM
NC3 ORG 06/24/08 01:00:51 DBPROD18.NOC.ATT.COM
NC3 ORG_ORG 06/24/08 01:00:51 DBPROD18.NOC.ATT.COM
NC3 PARM 06/24/08 02:00:06 DBPROD18.NOC.ATT.COM
NC3 PARM_REF 06/24/08 01:01:02 DBPROD18.NOC.ATT.COM
NC3 PARM_REQ_RUL 06/24/08 01:01:44 DBPROD18.NOC.ATT.COM
NC3 PERSON 06/24/08 01:01:48 DBPROD18.NOC.ATT.COM
NC3 PERSON_TO_PO 06/24/08 01:01:59 DBPROD18.NOC.ATT.COM
NC3 PHY_CIRCUIT 06/24/08 01:02:03 DBPROD18.NOC.ATT.COM
NC3 PHY_CIRCUIT_ETV 06/24/08 01:02:07 DBPROD18.NOC.ATT.COM
NC3 PHY_DELETE 06/24/08 01:02:48 DBPROD18.NOC.ATT.COM
NC3 PORT 06/24/08 01:00:47 DBPROD18.NOC.ATT.COM
NC3 PORT_REF 06/24/08 01:02:53 DBPROD18.NOC.ATT.COM
NC3 PORT_REF_COMPAT 06/24/08 01:03:05 DBPROD18.NOC.ATT.COM
NC3 POTS_DIAL_PEER 06/24/08 01:03:05 DBPROD18.NOC.ATT.COM
NC3 PRIORITY_QUEUE 06/24/08 01:03:08 DBPROD18.NOC.ATT.COM
NC3 PROCESS_CONTROL 06/24/08 01:03:11 DBPROD18.NOC.ATT.COM
NC3 PROTOCOL 06/24/08 01:03:15 DBPROD18.NOC.ATT.COM
NC3 PROTOCOL_REF 06/24/08 01:03:15 DBPROD18.NOC.ATT.COM
NC3 PVC 06/24/08 01:03:11 DBPROD18.NOC.ATT.COM
NC3 PVC_ETV 06/24/08 01:03:17 DBPROD18.NOC.ATT.COM
NC3 PVC_GB 06/24/08 01:00:11 DBPROD18.NOC.ATT.COM
NC3 PVC_GB_LOG_PORT 06/24/08 01:00:11 DBPROD18.NOC.ATT.COM
NC3 P_LOG_P_REF 06/24/08 01:00:11 DBPROD18.NOC.ATT.COM
NC3 ROUTER_EXT 06/24/08 01:01:09 DBPROD18.NOC.ATT.COM
NC3 SDLC_PARTNER_DEV 06/24/08 01:00:11 DBPROD18.NOC.ATT.COM
NC3 SITE 06/24/08 01:01:14 DBPROD18.NOC.ATT.COM
NC3 SLOT 06/24/08 01:00:11 DBPROD18.NOC.ATT.COM
NC3 SLOT_EQP_C_R 06/24/08 01:00:14 DBPROD18.NOC.ATT.COM
NC3 SLOT_EQP_REF 06/24/08 01:00:14 DBPROD18.NOC.ATT.COM
NC3 SLOT_REF 06/24/08 01:00:22 DBPROD18.NOC.ATT.COM
NC3 SNA_DEV_PROPERTY 06/24/08 01:00:22 DBPROD18.NOC.ATT.COM
NC3 SNA_TRADE_PARTNER 06/24/08 01:00:28 DBPROD18.NOC.ATT.COM
NC3 SUPPLIER_PART_NUM 06/24/08 01:03:13 DBPROD18.NOC.ATT.COM
NC3 SVC_NODE_PREFIX 06/24/08 01:00:28 DBPROD18.NOC.ATT.COM
NC3 SVC_PORT_ADDRESS 06/24/08 01:00:28 DBPROD18.NOC.ATT.COM
NC3 SVC_PORT_PREFIX 06/24/08 01:00:31 DBPROD18.NOC.ATT.COM
NC3 TIME_RANGE 06/24/08 01:00:38 DBPROD18.NOC.ATT.COM
NC3 TRAFFIC_CLASS 06/24/08 01:03:14 DBPROD18.NOC.ATT.COM
NC3 TRAFFIC_SHAPE 06/24/08 01:03:15 DBPROD18.NOC.ATT.COM
NC3 USER_CRED 06/24/08 01:00:39 DBPROD18.NOC.ATT.COM
NC3 USER_GROUP 06/24/08 01:00:40 DBPROD18.NOC.ATT.COM
NC3 USER_PROF 06/24/08 01:00:42 DBPROD18.NOC.ATT.COM
NC3 USER_RESTRICTED_ACCESS 06/24/08 01:00:40 DBPROD18.NOC.ATT.COM
NC3 VAL_VAL 06/24/08 01:00:41 DBPROD18.NOC.ATT.COM
NC3 VAL_VAL_TYPE 06/24/08 01:00:41 DBPROD18.NOC.ATT.COM
NC3 VERSION_CHK 06/24/08 01:00:41 DBPROD18.NOC.ATT.COM
NC3 VOIP_DIAL_PEER 06/24/08 01:00:41 DBPROD18.NOC.ATT.COM
133 rows selected.
SQL> l
1 select * from all_BASE_TABLE_MVIEWS@NC3_MAT_LINK where MVIEW_ID not in (select mview_id from all_Registered_mviews@NC3_MAT_LINK)
2* order by mview_id
SQL> /
OWNER MASTER MVIEW_LAS MVIEW_ID
------------------------------ ------------------------------ --------- ----------
NC3 CHNNL 20-JUN-08 2
NC3 AEQP 20-JUN-08 3
NC3 AATMPVC 20-JUN-08 4
NC3 ACONFIG 20-JUN-08 5
NC3 ALOCN 20-JUN-08 6
NC3 AMRPTST 20-JUN-08 7
NC3 ANX_EXT 20-JUN-08 8
NC3 APVC 20-JUN-08 9
NC3 ASITE 20-JUN-08 10
NC3 ATM_PVC 20-JUN-08 11
NC3 ATM_SIGNALLING 20-JUN-08 12
NC3 ATM_TRAFFIC 29-JUN-07 13
NC3 ATM_USER_PREF 20-JUN-08 14
NC3 AUTH 20-JUN-08 15
NC3 AUTH_TOKEN 20-JUN-08 16
NC3 BOX_PROF 20-JUN-08 17
NC3 CARRIED_CKT 20-JUN-08 18
NC3 CARRIED_CKT_ETV 20-JUN-08 19
NC3 CDVR_QA_CFG 20-JUN-08 20
NC3 CHANGE_HISTORY 20-JUN-08 21
NC3 CHNNL_CARRIED_CKT 20-JUN-08 22
NC3 CIRCUIT_LOC 20-JUN-08 23
NC3 CIRCUIT_REF 20-JUN-08 24
NC3 CMP_PARM_REF 20-JUN-08 25
NC3 CMP_PROFILE 20-JUN-08 26
NC3 CMP_PROFILE_CMP 20-JUN-08 27
NC3 CMP_PROFILE_USAGE 20-JUN-08 28
NC3 CMP_TO_CMP 18-OCT-07 29
NC3 CMP_USAGE 20-JUN-08 30
NC3 COMPONENT_COORD 20-JUN-08 31
NC3 COMPONENT_OBJ_REF 20-JUN-08 32
NC3 CONFIG 20-JUN-08 33
NC3 CONFIG_CHNNL_REF 20-JUN-08 34
NC3 CONFIG_REF 20-JUN-08 35
NC3 CONNECTION 20-JUN-08 36
NC3 CONNECTOR_COORD 20-JUN-08 37
NC3 CONN_REF 20-JUN-08 38
NC3 CONTAIN 20-JUN-08 39
NC3 CONT_RSRC 20-JUN-08 40
NC3 COS_CDR 21-JUN-08 41
NC3 COS_TRAFFIC_APPL 20-JUN-08 42
NC3 COS_TRAFFIC_IP 20-JUN-08 43
NC3 COS_TRAFFIC_IP_PREC 20-JUN-08 44
NC3 CUSTOM_QUEUE 20-JUN-08 45
NC3 DEFINED_PATH 20-JUN-08 46
NC3 EQP 20-JUN-08 47
NC3 EQPORT_EQPORT_REF 20-JUN-08 48
NC3 EQP_LOC 20-JUN-08 49
NC3 EQP_PORT_REF 20-JUN-08 50
NC3 EQP_REF 20-JUN-08 51
NC3 EQP_REF_DOC 20-JUN-08 52
NC3 EQP_SLOT_REF 20-JUN-08 53
NC3 FREEFORM_TEXT 20-JUN-08 54
NC3 ILMI 20-JUN-08 55
NC3 INFOCONN 20-JUN-08 56
NC3 IPX_TRADE_PARTNER 20-JUN-08 57
NC3 IP_TRADE_PARTNER 20-JUN-08 58
NC3 ISDN_CHANNEL 20-JUN-08 59
NC3 ISDN_LINE 20-JUN-08 60
NC3 LAN_SEGMENT 20-JUN-08 61
NC3 LOCATION 20-JUN-08 62
NC3 LOG_PORT 20-JUN-08 63
NC3 LOG_PORT_REF 20-JUN-08 64
NC3 MISC_CMP 20-JUN-08 65
NC3 MISC_CMP_REF 20-JUN-08 66
NC3 MULT_CAST 20-JUN-08 67
NC3 MULT_MEM 20-JUN-08 68
NC3 NETBIOS_TRADE_PART 20-JUN-08 69
NC3 NEWODBID 20-JUN-08 70
NC3 ORDERS 20-JUN-08 71
NC3 ORDER_CONFIG 20-JUN-08 72
NC3 ORD_CMP_ORG 06-SEP-07 73
NC3 ORG_ORG 20-JUN-08 75
NC3 PARM_REF 20-JUN-08 77
NC3 PARM_REQ_RUL 20-JUN-08 78
NC3 PERSON 20-JUN-08 79
NC3 PERSON_TO_PO 20-JUN-08 80
NC3 PHY_CIRCUIT 20-JUN-08 81
NC3 PHY_DELETE 20-JUN-08 83
NC3 PORT_REF 20-JUN-08 85
NC3 PORT_REF_COMPAT 20-JUN-08 86
NC3 POTS_DIAL_PEER 20-JUN-08 87
NC3 PRIORITY_QUEUE 20-JUN-08 88
NC3 PROCESS_CONTROL 20-JUN-08 89
NC3 PROTOCOL 20-JUN-08 90
NC3 PROTOCOL_REF 20-JUN-08 91
NC3 PVC_ETV 20-JUN-08 93
NC3 PVC_GB 20-JUN-08 94
NC3 PVC_GB_LOG_PORT 20-JUN-08 95
NC3 P_LOG_P_REF 20-JUN-08 96
NC3 ROUTER_EXT 20-JUN-08 97
NC3 SDLC_PARTNER_DEV 20-JUN-08 98
NC3 SITE 20-JUN-08 99
NC3 SLOT 20-JUN-08 100
NC3 SLOT_EQP_C_R 20-JUN-08 101
NC3 SLOT_EQP_REF 20-JUN-08 102
NC3 SLOT_REF 20-JUN-08 103
NC3 SNA_DEV_PROPERTY 20-JUN-08 104
NC3 SNA_TRADE_PARTNER 20-JUN-08 105
NC3 SVC_NODE_PREFIX 20-JUN-08 106
NC3 SVC_PORT_ADDRESS 20-JUN-08 107
NC3 SVC_PORT_PREFIX 20-JUN-08 108
NC3 TIME_RANGE 20-JUN-08 109
NC3 USER_CRED 20-JUN-08 111
NC3 USER_GROUP 20-JUN-08 112
NC3 USER_PROF 28-JUN-07 113
NC3 VAL_VAL 20-JUN-08 114
NC3 VAL_VAL_TYPE 20-JUN-08 115
NC3 VERSION_CHK 20-JUN-08 116
NC3 VOIP_DIAL_PEER 20-JUN-08 117
NC3 IP_INTERFACE 20-JUN-08 121
NC3 IP_HELPER 20-JUN-08 122
NC3 USER_RESTRICTED_ACCESS 20-JUN-08 123
NC3 ORG 20-JUN-08 124
NC3 PHY_CIRCUIT_ETV 20-JUN-08 141
NC3 PVC 20-JUN-08 161
116 rows selected.
June 25, 2008 - 8:28 am UTC
first, use the DBA views, else you might not be seeing "everything"
second, why dump entire data dictionary tables here? why not filter then by - oh - the information you need to diagnose your problem (for a single mv log one would presume)
use the REGISTERED mviews view, it has the defining query you are looking for.
Megala, June 25, 2008 - 11:01 am UTC
thanks Tom.
One question, How to find the target site materialized view details using master site MVIEW_ID (registered) ?
June 25, 2008 - 11:45 am UTC
define what you mean by this:
...How to find the target site materialized view details using.....
A reader, June 25, 2008 - 6:06 pm UTC
<< define what you mean by this:
...How to find the target site materialized view details using.....
On master site, i have mview_ids shown from user_registed_mviews . Does oracle store this mview_id info in target materialized view site. thanks
June 26, 2008 - 10:22 am UTC
not that I am aware of, you have the text of the materialized view in the master site to refer to.
Remove from the log.
A reader, November 19, 2008 - 9:02 pm UTC
Hi Tom,
Say I have two MV's S1 and S2 for a master table, how oracle knows which one is the lastest mv that need refresh.
Thanks
November 24, 2008 - 11:22 am UTC
look int dba_mviews... we record metadata there about the time of last refresh.
Refresh Activity between Update and Rolback
Vipul, January 14, 2009 - 7:47 am UTC
Hi Tom,
I have a query related the sequence of events you had explained. i.e.
1. Update record
2. Insert into MLOG
3. If Rollback -> Delete the MLOG record.
4. If Commit -> The record stays till refreshed.
Now in case the fast refresh activity happens between the step 2 and 3 (assuming i perform a rollback),Mviews would find a record in MLOG and would refresh itself. Assuming ROLLBACK statement doesnt insert a record in MLOG , wouldn't the MVIEW be having incorrect information?
How does Oracle resolve this? Does ROLLBACK statement also inserts a record in MLOG?
Many thanks,
Vipul
January 16, 2009 - 4:43 pm UTC
until you commit - a record you modify or create (or delete) would appear UNCHANGED to the outside world.
until you commit, that change is not visible to anything or anyone else.
Locks with On commit refresh
Chinni, January 27, 2009 - 4:27 am UTC
Hi Tom,
I have a process like this
Loop
Update Table t1;
commit;
End loop;
I have two "on commit" mviews on table t1, two triggers also. Now I have many many locked sessions in my database.
This process(above loop) has blocked a session, which is happened to be refreshing the dependent mviews of T1 and again this refreshing process is blocked by the original process(loop). Could you please tell me whether the "on commit" refresh is cause of concern when it comes to committing in a loop like above?
I know that this is definitely not enough information for you to understand my problem, but if you could comment some thing that will be much appreciate.
Thanks
January 28, 2009 - 8:33 am UTC
... I know that this is definitely not enough information for you to understand my
problem, but if you could comment some thing that will be much appreciate.
...
that made me laugh.
every time you commit (really bad style to commit in that loop like that, I'll bet the code is NOT safely restartable so when it fails, you are left with a mess...) you will refresh the Materialized view (MV). Typically the MV would be an aggregate.
For example, lets say the MV was simply "select count(*) from t".
The MV has one row. Every commit would want to update this one row. That means commits will serialize - one at a time, no concurrent commits since they each need to update the same record.
and yes, since you are constantly updating the MV, and another process needs exclusive access to the MV (to do the refreshes) you run into other concurrency (lack thereof) issues.
But it should not block it indefinitely - that is, the mview refresh that got blocked - should unblock after the commit finishes - and then it (the mview refresh) should start blocking the commit for a short period - and then everything should get moving again.
But, I'd need to see a small concrete example before commenting further.
Thank You .. I posted this in another thread
Chinni, February 02, 2009 - 12:29 pm UTC
Hi Tom,
...
that made me laugh.
....
Me too . finally I made you laugh .. :)
Thanks for the reply!
..
But it should not block it indefinitely - that is, the mview refresh that got blocked - should unblock after the commit finishes - and then it (the mview refresh) should start blocking the commit for a short period - and then everything should get moving again.
..
But it did actually block indefinitely .. I posted this under another thread..(sorry i missed this post here)
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4541191739042#1464016300346059355 ..
and you suggested to contact support ..
anyhow a great learning on your site .. many thanks
MV logs, waiting for Godot
Mike, February 26, 2009 - 7:20 am UTC
We have a number of MVs on our Data Warehouse database that pull data from an OLTP database. Some of these use fast refresh, and therefore use MV logs on the OLTP database.
I discovered that our MV logs are not cleaning out after refreshes. I have followed this problem back to this: when we moved the data warehouse database a while ago, the MVs were re-created on the new database but not actually dropped on the old one. So DBA_REGISTERED_MVIEWS shows both the old and new MVs - for example:
1 select owner, name, mview_site
2 from dba_registered_mviews
3* where owner = 'MTRSPROD' and name = 'WHL_HUDLINE_MV'
06:41:55 > /
OWNER NAME MVIEW_SITE
------------------------------ ------------------------------ ----------------------------
MTRSPROD WHL_HUDLINE_MV PEDW.WORLD
MTRSPROD WHL_HUDLINE_MV PFDW.WORLD
2 rows selected.
Elapsed: 00:00:00.01
06:41:56 >
Now, PFDW.WORLD is gone, so that MV never refreshes, so the rows in the MV log never go away - they are being retained in anticipation of that refresh.
I have set up a similar test scenario in our development databases, and have verified
- with 1 MV, the log cleans out after refresh
- with 2 MVs, the log cleans out after both are refreshed
I tried DBMS_MVIEW.UNREGISTER_MVIEW to unregister the second MV. It no longer appears in DBA_REGISTERED_MVIEWS, but the rows are apparently still being held for it. Even after another refresh of MV #1 the MV log still has the rows that MV#2 may someday need.
What do I need to do, to allow these rows in the MV logs to be purged?
March 03, 2009 - 7:40 am UTC
mview log growth issue
A reader, July 24, 2009 - 3:51 am UTC
Tom,
As we know materialized view log grows (by size as it's only deleting rows not releasing space) over period of time and their performance does get deteriorate if they grow big.
So we have to use method to truncate MLOG$_<table> like,
Lock a master in one session, do a refresh of mviews from all mview sites and then truncate mlog$_<table> in another session and then release lock from the first session.
So my query is can we automate this process. But only problem I am facing is how to do it one session using PL/SQL, how can we fork a process from PL/SQL? Can this be done?
Thanks,
Deepank
July 26, 2009 - 7:07 am UTC
As we know materialized view log grows (by size as it's only deleting rows not
releasing space) over period of time and their performance does get deteriorate
if they grow big.
who is this WE you refer to? Because *I* do not know this "fact", which is not a fact.
What is the underlying cause of your log growing? Do you have a REGISTERED materialized view that is not being refreshed???
A reader, July 29, 2009 - 3:18 am UTC
Hi Tom,
According to Note 1031924.6 logs also grows due missing or invalid snapshots. This is not the issue our case.
Let me rephrase. Say for example I have one master and 2 mview site. Say there are huge DML performed on master then there would be entries in MLOG$ table. When the last MVIEW gets refreshed then it will delete all rows which are not required. So now next time fast refresh happen oracle need to scan these grown extents (FTS on MLOG$) for deletion. So it is vital to keep check on MLOG$ growth and do their truncation as I mentioned in my previous post. It's recommended method by oracle (please refer metalink doc 236233.1 Log Population and Purge).
Please let me know if I have misunderstood any of these concepts.
Thanks
Deepank
July 29, 2009 - 3:31 am UTC
Rephrasing is good, since this time you explicitly mention a specific case that many people would never encounter - rather then saying
... As we know materialized view log grows (by size as it's only deleting rows not releasing space)
over period of time and their performance does get deteriorate if they grow big.
So we have to use method to truncate MLOG$_<table> ...
which is not a true statement.
You can also just shrink the segment as well in 10g and above...
is it "vital to keep check", no I think that overstates it, far too scary sounding. Is it something to be aware of? Yes. Does Enterprise Manager tell you about it? Yes, the segment advisor would note this and let you know.
A reader, July 29, 2009 - 7:24 am UTC
Thanks Tom. I got your point.
In our case these kinds of adhoc DML's are more so it's vital in our case to keep the check as there are 8 MVIEW site and Prod DB is loaded at a times.
As you suggested online shrink seems good one but I haven't tried it on MLOG$_<table>.
Apart from this, is there way to fork a process in PL/SQL if we want to automate it.
Thanks,
Deepank
July 29, 2009 - 7:27 am UTC
there is no 'fork' in the sense of fork() in Unix land.
there are the dbms_job and dbms_scheduler packages you can use to start other independent sessions to run stuff.
A reader, July 30, 2009 - 10:09 am UTC
Thanks Tom.
Tom is not in town
A reader, August 03, 2009 - 8:25 am UTC
Tom seems to be busy as his calendar shows .
never in last few months I have observed that there has been no post from him for almost 5 days now!! seems too long .
August 04, 2009 - 1:53 pm UTC
well... I spent 24 hours in a plane from Singapore to back home thursday/friday.
and then it was... the weekend.
and normal programming resumed right after you posted this on monday morning.
Still feel jet lagged from that one, Singapore is 12 hours off of my timezone.
Same MVIEW_ID for two mview on different sites
Fab, March 23, 2010 - 10:54 am UTC
Hi Tom,
I checked the all_registered_mviews on my master site and I was surprised to see that several mviews have the same MVIEW_ID and NAME, but a different owner.
SELECT mview_id, name, COUNT(*) FROM all_registered_mviews HAVING COUNT(*) > 1 GROUP BY mview_id, name
And unfortunatly, I have problems when refreshing some of these views on both remote sites (the refresh (fast or complete) never stops and I have no error message). Can this be the cause of my problems ?
Version 10.2.0.2
Thanks
March 23, 2010 - 12:52 pm UTC
no, but please do utilize support (which you have, 10.2.0.2 is a patch release) to diagnose this.