Thanks for the question, Praveen.

Asked: August 23, 2016 - 4:01 am UTC

Last updated: August 26, 2016 - 7:51 am UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

GM Tom,

In my current db implementation, we do not have any data/tables in our db and gets all data from two other data sources. To do so we have created Materialized Views using dblinks to get data from these two other db sources. We use to COMPLETE refresh our Materialized Views daily at off business hours which takes approx 3 hours.

Both the sources databases are in Oracle. My Oracle version is 11.2.

We have got new business requirement that we have to now refresh our MVs with FAST method during business hours.

We initially thought to create MVs with FAST refresh on COMMIT. But later get to know over internet that we can not create ON COMMIT FAST refresh on remote dbs. Please correct me if i am wrong here.

Can we create MVs with FAST REFRESH ON DEMAND on remote dbs?

I understand that we also need to have MV logs on source tables. I am still investigating if the source is TABLES only or are they VIEWS as well.

Can we create MV logs on views at source?

This is the first time i am going to implement MV with FAST refresh option that too at remote dbs.

Request you to please help me with the steps/sources with best possible way to implement MV with FAST refresh ON COMMIT/DEMAND on remote dbs.

and Connor said...

Yes you can have remote fast refresh, eg

<code>
--
-- database 'db11'
--
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t
2 as select * from dba_objects
3 where object_id is not null;

Table created.

SQL>
SQL> alter table t add constraint t_pk primary key ( object_id );

Table altered.

SQL>
SQL> create materialized view log on t ;

Materialized view log created.

SQL>
SQL> select count(*) from t;

COUNT(*)
----------
86274

1 row selected.

--
-- database 'db12'
--

SQL> create materialized view t_mv
2 build immediate
3 refresh fast
4 on demand
5 as
6 select * from t@db11;

Materialized view created.

SQL> select count(*) from t_mv;

COUNT(*)
----------
86274

--
-- database 'db11'
--
SQL> delete from t where rownum < 10;

9 rows deleted.

SQL> commit;

Commit complete.

SQL>
--
-- database 'db12'
--
SQL> exec dbms_mview.refresh('T_MV')

PL/SQL procedure successfully completed.

SQL> select count(*) from t_mv;

COUNT(*)
----------
86265
<code>

If the remote objects are views...that is still possible, but you need materialized views logs on each of the source tables, and your materialized view would then be the same the as the view definition.

But depending on the complexity of the view definitions, you might not be able to perform *fast* refresh. Check the docs for DBMS_MVIEW.EXPLAIN_MVIEW which can report on why an MV can be fast refreshed (or not)

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Praveen, August 23, 2016 - 8:02 am UTC

Thanks a lot Connor McDonald for your valuable feedback. I shall implement the FAST refresh suggested by you and shall ask you in case find any further issue. Thanks.

Getting error while creating fast refresh MV

Praveen, August 24, 2016 - 1:53 pm UTC

request you to please suggest where i am doing wrong in code as i am getting error "ORA-00942: table or view does not exist" while creating materialized view with FAST refresh using db link. I have already created MV log on source table using command,

"CREATE Materialized View Log ON tbl_HB_SYSTEM;"

and my create MV script is

CREATE materialized view HB_SYSTEM BUILD IMMEDIATE REFRESH FAST ON DEMAND WITH primary KEY AS SELECT ID, DELETED_YN, SYS_NO FROM HB.tbl_HB_SYSTEM@HBLINK;

I have read in some forums that grant select privilege is required on MV Logs but i do not know the exact query/command to grant privilege. Kindly suggest the resolution for the same.

Getting error while creating fast refresh MV

Praveen, August 24, 2016 - 2:28 pm UTC

I have also found your comment in article

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9527231800346481360

To grant:

GRANT SELECT ON MLOG$_<tableName> to XXX;

where XXX is the account that will accessing the log (ie via the db link).

I have two dbs (dbONE and dbTWO). I have to create MV fast refresh in dbTWOto replicate table in dbONE locally. To do so i have create MV log in db1 as well. But while trying to grant select on MV Log using bellow query i am getting error. I have executed this query from dbONE connection.

GRANT SELECT ON MLOG$_TBL_SYSTEM TO dbTWO_User;

The error is "SQL Error: ORA-01917: user or role 'dbTWO_User' does not exist"

But i am able to select records using dblink from dbTWO

SELECT ID, DELETED_YN, SYS_NO FROM HB.tbl_HB_SYSTEM@HBLINK;

Please suggest how to resolve these errors to get MV with FAST refresh be created.

August 25, 2016 - 3:10 am UTC

A database link connects to remote database like a client would, eg

create database link HBLINK
connect as SCOTT
identified by TIGER
using 'dbtwo';

So when you run

select * from HB.tbl_HB_SYSTEM@HBLINK

it will be doing so as the user defined in the database link (in my example above it would be SCOTT).

So you need to grant select on the relevant tables, and mview logs to SCOTT.

Thanks, It worked on grant select to user

Praveen, August 25, 2016 - 1:44 pm UTC

Thanks Connor McDonald, it worked after granting access to the user as suggested by you.

Just wanted to know one more thing, can we join more than one remote table (using dblink) while creating MV FAST REFRESH ON DEMAND WITH PRIMARY KEY.

CREATE materialized view HB_SYSTEM
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
WITH PRIMARY KEY 
AS 
SELECT * FROM HB.HB_SYSTEM@HBLINK hbsys
LEFT OUTER JOIN HB.HB_SUB@HBLINK hbsub ON hbsys.ID = hbsub.HB_sys_Id;

We have also created MV Logs on source(remote table) HB_SYSTEM and HB_SUB.

I am getting as below when i tried to run this above query.

SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query

Kindly suggest how can we create MV Fast Refresh with joins.

One more suggestion please, is it possible to join source tables(using dblinks) with local existing tables/MVs. For example,

CREATE materialized view HB_SYSTEM
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
WITH PRIMARY KEY 
AS 
SELECT * FROM HB.HB_SYSTEM@HBLINK hbsys
LEFT OUTER JOIN HB.HB_SUB hbsub ON hbsys.ID = hbsub.HB_sys_Id
LEFT OUTER JOIN DSHB.MV_HB_Order Ord ON Ord.HB_sys_Id = hbsys.ID;

August 26, 2016 - 2:07 am UTC

There are rules for what MV's are fast refreshable, in particular those with joins.

See the docs

http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#DWHSG8181

You may need to refresh each table to your local database, and do the join locally.

Thanks for valuable comments

Praveen Shrivas, August 26, 2016 - 3:39 am UTC

Hi Connor McDonald,

Thanks for your valuable inputs for my query.
Really helpful.

August 26, 2016 - 7:51 am UTC

glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Getting error while creating fast refresh MV

Getting error while creating fast refresh MV

Thanks, It worked on grant select to user

Thanks for valuable comments

More to Explore

PL/SQL demos

PL/SQL docs

Oracle Materialized View Fast refresh on remote database

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Getting error while creating fast refresh MV

Getting error while creating fast refresh MV

Thanks, It worked on grant select to user

Thanks for valuable comments

More to Explore

PL/SQL demos

PL/SQL docs