I was trying to create Materialized view with refresh on commit on linked server table, but so far no luck. Also not sure if that's possible or not since in oracle doc, its nothing said about this being possible or not.
I am able to create normal materialized views with refresh fast/complete, but i cant make it to work with on commit. As i understand from documentation, that i need to create MATERIALIZED VIEW LOG ON "table_name", but oracle do not let me point this table to linked one
Code i tried:
CREATE MATERIALIZED VIEW LOG ON (SCHEMA_NAME.TABLE_NAME@DATABASE_LINK1)
WITH ROWID (col1, col2, col3, col4, col5),
INCLUDING NEW VALUES;
The latter one works, if I change REFRESH FAST to REFRESH NEXT TRUNC(SYSDATE) + 1
CREATE MATERIALIZED VIEW MYSCHEME_NAME.TABLE_NAME
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT col1, col2, col3, col4, col5
FROM SCHEMA_NAME.TABLE_NAME@DATABASE_LINK1;
Maybe someone can suggest anouther approach?
I cant use normal VIEWS, since they are too slow, but i would like data to be up-date as much as possible, so in worst case, i will just use REFRESH NEXT TRUNC(SYSDATE) + 1 although i rly dont like idea of refreshing view with no reason.
EDIT: I just added the Continuous Query Notification option down below
I see your point, but given that you are talking about two different databases at once, more than a view it looks more like a problem of almost-real-time data replication.
On database A you have data that, once committed, must be replicated on database B (slightly transformed, like just having less columns).
You could probably may have a look at Streams (that are tailored to replication problems), for instance starting here: Simple Single-Source Replication Example. By its very nature, with Streams a change is replicated only upon commit, not earlier.
It may be overkill though, because despite the "Simple" in the title, you'll see that the "simple" example is rather long.
If you are going that way, have also a look at Managing Rule-Based Transformations, to transform the data during the replication.
To refresh the view you might define a job. In order to refresh it on a daily basis, but only if the table has changed, you can keep track of the latest SCN of the table (a marker that tells the version of the record/block) and (daily) refresh your view but only if it the latest SCN has changed (this is ugly, sort of poor's man replication). All this 'refresh' logic should be placed in the job's body.
--example of how to select the most current SCN
select MAX(ora_rowscn) from SCHEMA_NAME.TABLE_NAME@DATABASE_LINK1;
--example of how to submit a job executed on a daily basis
exec dbms_job.submit(:v_JobNo,
'a string representing the PL/SQL logic that refreshes the view only when the scn has changed',
TRUNC(SYSDATE)+1,
'TRUNC(SYSDATE)+1'
);
CQN allows to define a callback to be invoked when the results of a query change (see ORACLE-BASE - DBMS_CHANGE_NOTIFICATION in Oracle 10g Database Release 2). You may use this on the remote database to trigger a refresh of the view on the local database when new records are added.
Give it a try because I am not sure that a notification on the remote database can trigger a callback procedure that resides on a different database (the local database).
This is the kind of code I would play with (and tune to my needs; for instance the COUNT(*) in the query works but has awful performances)
On the local DB define your view and a procedure that refreshes the latter. That same procedure will be invoked as a callback on the remote DB.
drop procedure refresh_materialized_view;
drop materialized view mat_view;
create materialized view mat_view
as select * from monitored_table@remote_db;
/
--this local procedure refreshes the view
create or replace procedure refresh_materialized_view is
begin
--C stands for Complete refresh
DBMS_SNAPSHOT.REFRESH( 'MAT_VIEW','C');
end;
/
On the remote DB register the Change Notification to call the callback defined above. Mind the permissions, you may need to grant change notification to the proper user on this database
create table monitored_table(n number, ts timestamp);
insert into monitored_table values(1, systimestamp);
commit;
DECLARE
l_regds SYS.CHNF$_REG_INFO;
l_regid NUMBER;
l_qosflags NUMBER;
v_n monitored_table.n%TYPE;
BEGIN
l_qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE +
DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS;
--invoke REFRESH_MATERIALIZED_VIEW@MY_DB when a change is detected in the table (see query below)
l_regds := SYS.CHNF$_REG_INFO ('MY_SCHEMA.REFRESH_MATERIALIZED_VIEW@MY_DB', l_qosflags, 0,0,0);
l_regid := DBMS_CHANGE_NOTIFICATION.new_reg_start (l_regds);
--upon changes on the results returned from this query, a notification will be triggered
SELECT count(*)
INTO v_n
FROM monitored_table;
DBMS_CHANGE_NOTIFICATION.reg_end;
END;
/
Add records to trigger the notification:
--trigger a Query Change
insert into monitored_table values(2, systimestamp);
insert into monitored_table values(3, systimestamp);
--note that the notification is triggered once for each commit
commit;
And finally check if your view was refreshed
select * from mat_view;
Whatever you do, anyway, beware of the price to pay in terms of performance. It you are monitoring a table that contains logs, chances are that it is a high volume/frequency data, so you might not want to sync your view frequently or with each commit.