Home » Server Options » Replication » Materialized View (Oracle 10g)
Materialized View [message #464233] Wed, 07 July 2010 01:17 Go to next message
abdulahads
Messages: 44
Registered: October 2009
Location: SAUDI
Member

Please can any one give me a simple example to
Create Materialized view with Refresh on commit example.
with create Log file example.

Thanks in advance
Re: Materialized View [message #464234 is a reply to message #464233] Wed, 07 July 2010 01:21 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member

CREATE MATERIALIZED VIEW

Example


Regards
Ved
Re: Materialized View [message #464242 is a reply to message #464234] Wed, 07 July 2010 01:42 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Hi - if you consider the mechanism of a REFRESH ON COMMIT materialized view, is a log needed?
Re: Materialized View [message #464247 is a reply to message #464242] Wed, 07 July 2010 02:05 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Yes.ON COMMIT MVIEW logs must be built as ROWID logs, not as primary-key logs.

Regards
Ved
Re: Materialized View [message #464248 is a reply to message #464242] Wed, 07 July 2010 02:08 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Good Question!
IMO not required,Because the log uses to refresh the view for the base table changes.
If we use "REFRESH ON COMMIT" the same refresh happend automatically without a log.
And seniors let me know If I am missing anyinfo here.
Thank you
sriram Smile

[Updated on: Wed, 07 July 2010 03:43]

Report message to a moderator

Re: Materialized View [message #464250 is a reply to message #464247] Wed, 07 July 2010 02:18 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Its_me_ved wrote on Wed, 07 July 2010 02:05
Yes.ON COMMIT MVIEW logs must be built as ROWID logs, not as primary-key logs.



Regards
Ved


My apologies I was wrong


SQL> create materialized view mv2
  2  REFRESH ON COMMIT
  3  as select col from g501;

Materialized view created.

Re: Materialized View [message #464270 is a reply to message #464250] Wed, 07 July 2010 03:37 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
No log needed:
jw> create table t1 as select * from all_users;

Table created.

jw> desc t1
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- -------------------
 USERNAME                                              NOT NULL VARCHAR2(30)
 USER_ID                                               NOT NULL NUMBER
 CREATED                                               NOT NULL DATE

jw> alter table t1 add constraint t1pk primary key(user_id);

Table altered.

jw> create maerialized view mv1 refresh on commit as select * from t1;
create maerialized view mv1 refresh on commit as select * from t1
       *
ERROR at line 1:
ORA-00901: invalid CREATE command


jw> create materialized view mv1 refresh on commit as select * from t1;

Materialized view created.

jw> selectcount(*) from mv1;
SP2-0734: unknown command beginning "selectcoun..." - rest of line ignored.
jw> select count(*) from mv1;

  COUNT(*)
----------
        44

jw> delete from t1;

44 rows deleted.

jw> select count(*) from mv1;

  COUNT(*)
----------
        44

jw> commit;

Commit complete.

jw> select count(*) from mv1;

  COUNT(*)
----------
         0

jw>
Previous Topic: Oracle GoldenGate
Next Topic: GoldenGate Environment Variable
Goto Forum:
  


Current Time: Fri Mar 29 09:29:27 CDT 2024