OraFAQ Forum: SQL & PL/SQL » Materialized View

Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View (Window 10, 11G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Materialized View [message #646341]

Mon, 28 December 2015 02:57

Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan

Senior Member

Dear All
I want to create materialized view for update but it display error message:

SQL> Create materialized view mv_emp for update
  2  refresh fast
  3  on commit
  4  as select * from employees;
refresh fast
*
ERROR at line 2:
ORA-00905: missing keyword

While when i create materialized view without for update it is successfully created.

SQL> Create materialized view mv_emp
  2  refresh fast
  3  on commit
  4  as select * from employees;

Materialized view created.

Please suggest me that how can i create updateable materialize view.

Thanks

Report message to a moderator

Re: Materialized View [message #646342 is a reply to message #646341]

Mon, 28 December 2015 02:59

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You don't UPDATE a materialized view; it relies on data stored in its underlying table(s), so you have to REFRESH it (not UPDATE).

Report message to a moderator

Re: Materialized View [message #646343 is a reply to message #646342]

Mon, 28 December 2015 03:04

Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan

Senior Member

Actually i need it for replication,any suggestion for that?

As materialized view for update is created but i cant refresh it.

SQL> Create materialized view mv_emp1 for update
   2  as select * from employees;

Materialized view created.

Report message to a moderator

Re: Materialized View [message #646345 is a reply to message #646343]

Mon, 28 December 2015 03:19

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Bilal Khan wrote on Mon, 28 December 2015 10:04

Actually i need it for replication,any suggestion for that?

Have you considered just to follow the syntax supported by Oracle? You may find it e.g. in SQL Language Reference, which is available with other Oracle documentation books e.g. online on http://docs.oracle.com/en/database/database.html
For 11gR2, the relevant chapter is here: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6002.htm#SQLRF01302
Note, that order of clauses (REFRESH ..., FOR UPDATE) does matter.

Report message to a moderator

Re: Materialized View [message #646346 is a reply to message #646343]

Mon, 28 December 2015 04:01

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Replicated MVs can't refresh synchronously on COMMIT, you have to fast refresh them asynchronously from materialized view logs.

Report message to a moderator

Previous Topic:	Start DB session using sh script with out the password
Next Topic:	Can we Write PLSQL concept in WITH clause

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 17:08:25 CDT 2024