Toad World® Forums

Transaction Consistency of Materialized view from dblink


#1

Is it possible to get materialized views that are pulled from a dblink to be read consistent. I found this command:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

But it appears to only work for transactions in the instance it’s running on.


#2

docs.oracle.com/…/ds_txnman.htm

This may get you what you want.

“Because SCNs are synchronized at the end of a remote query, precede each remote query with a dummy remote query to the same site, for example, SELECT * FROM DUAL@REMOTE.”


#3

The problem with this is solution is the query is stored in the materialized view, and we want all 256 materialized view to be built to the same commit point (SCN). Ideally I would want to know thwe SCN prior to
the job starting and have each query be synced to that SCN number.

Arnold Weinstein - Data System Analyst

ITS - Information Technology Services

University of California Office of the President

300 Lakeside Drive, 12th Floor 12064| Oakland CA 94612

Arnold.Weinstein@ucop.edu |(510) 587-6093

From: jpdominator [mailto:bounce-jpdominator@toadworld.com]

Sent: Tuesday, June 18, 2013 4:32 PM

To: oracle@toadworld.com

Subject: RE: [Toad World - Oracle Discussion Forum] Transaction Consistency of Materialized view from dblink

docs.oracle.com/…/ds_txnman.htm

This may get you what you want.

“Because SCNs are synchronized at the end of a remote query, precede each remote query with a dummy remote query to the same site, for example, SELECT * FROM DUAL@REMOTE.”


#4

Do all 256 materialized views come from the same materialized view log?

On Jun 18, 2013, at 7:00 PM, “arnold.weinstein” bounce-arnoldweinstein@toadworld.com wrote:

The problem with this is solution is the query is stored in the materialized view, and we want all 256 materialized view to be built to the same commit point (SCN). Ideally I would want to know thwe SCN prior to
the job starting and have each query be synced to that SCN number.

Arnold Weinstein - Data System Analyst

ITS - Information Technology Services

University of California Office of the President

300 Lakeside Drive, 12th Floor 12064| Oakland CA 94612

Arnold.Weinstein@ucop.edu |(510) 587-6093

UC_LOGO_90x90


#5

All 256 materialize view are passed on 256 distinct tables

Sent from my iPhone

On Jun 18, 2013, at 7:25 PM, “jpdominator” bounce-jpdominator@toadworld.com wrote:

Do all 256 materialized views come from the same materialized view log?

On Jun 18, 2013, at 7:00 PM, “arnold.weinstein” bounce-arnoldweinstein@toadworld.com wrote:

The problem with this is solution is the query is stored in the materialized view, and we want all 256 materialized view to be built to the same commit point (SCN). Ideally I would want to know thwe SCN prior to the job starting
and have each query be synced to that SCN number.

Arnold Weinstein - Data System Analyst

ITS - Information Technology Services

University of California Office of the President

300 Lakeside Drive, 12th Floor 12064| Oakland CA 94612

Arnold.Weinstein@ucop.edu |(510) 587-6093

UC_LOGO_90x90


#6

For all intents and purposes, I believe you could tailor this to your needs.

You want the lowest SCN from a list of MAX SCN’s derived from each materialized view.


declare
v_lowest_max_scn number(38) := 99999999999999999999999999999999999999;
v_eval_scn number(38);
begin
for i in (select distinct object_name as object_name from user_objects where object_type = 'MATERIALIZED VIEW') loop
execute immediate 'select max(ORA_ROWSCN) from '||i.object_name into v_eval_scn;
v_lowest_max_scn := least(v_lowest_max_scn, v_eval_scn);
end loop;
dbms_output.put_line(v_lowest_max_scn);
end;
/
SELECT * FROM mat_view1 where ora_rowscn <= :v_lowest_max_scn;
/

#7

After much experimentation I found the solution. The problem is I need the SCN from the DBLINK that the materialized views are pulled from. So the following gets me that information.

SELECT current_scn,
SYSTIMESTAMP
FROM
v$database@DBLINL.com;

Then I used the “AS OF SCN” or “AS OF TIMESTAMP” clause in the select statement of each materialized view

i.e.

SELECT *

FROM
ps_abbr_type_tbl@dblink.com

AS
OF
SCN 641237287;

SELECT *

FROM
ps_abbr_type_tbl@dblink.com

AS
OF
TIMESTAMP to_timstamp(‘2013-06-19 12:48:51.200012’,’YYYY-MM-DD hh24:mi:ss.ff’);

Arnold Weinstein - Data System Analyst

ITS - Information Technology Services

University of California Office of the President

300 Lakeside Drive, 12th Floor 12064| Oakland CA 94612

Arnold.Weinstein@ucop.edu |(510) 587-6093

From: jpdominator [mailto:bounce-jpdominator@toadworld.com]

Sent: Wednesday, June 19, 2013 12:41 PM

To: oracle@toadworld.com

Subject: RE: [Toad World - Oracle Discussion Forum] Transaction Consistency of Materialized view from dblink

RE: Transaction Consistency of Materialized view from
dblink

Reply by jpdominator

For all intents and purposes, I believe you could tailor this to your needs.

You want the lowest SCN from a list of MAX SCN’s derived from each materialized view.

declare
v_lowest_max_scn number(38) := 99999999999999999999999999999999999999;
v_eval_scn number(38);
begin
for i in (select distinct object_name as object_name from user_objects where object_type = 'MATERIALIZED VIEW') loop
execute immediate 'select max(ORA_ROWSCN) from '||i.object_name into v_eval_scn;
v_lowest_max_scn := least(v_lowest_max_scn, v_eval_scn);
end loop;
dbms_output.put_line(v_lowest_max_scn);
end;
/


SELECT * FROM mat_view1 where ora_rowscn <= :v_lowest_max_scn;
/

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Oracle
notifications altogether.

Toad World - Oracle Discussion Forum

Flag
this post as spam/abuse.