OraFAQ Forum: Replication

Home » Server Options » Replication » Replication (Oracle 9i (9.2.0.8) Linux)

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

Replication [message #359088]

Thu, 13 November 2008 12:54

smunir362
Messages: 306
Registered: September 2007

Senior Member

AOA
I have created three materalized view (MV1 MV2 MV3) from different databases into a single database.
Now my requirement is I want a table which include some fields from all my materialized views but insertable.

Pls help me or if you have any other point tell me.

Report message to a moderator

Re: Replication [message #359108 is a reply to message #359088]

Thu, 13 November 2008 14:20

babuknb
Messages: 1734
Registered: December 2005
Location: NJ

Senior Member

>>Now my requirement is I want a table which include some fields from all my materialized views but insertable

I am not able to understand your requirement.

Babu

Report message to a moderator

Re: Replication [message #359119 is a reply to message #359088]

Thu, 13 November 2008 15:30

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

Senior Member
Account Moderator

Would CTAS do the job? You know,

CREATE TABLE your_new_table
AS
SELECT mv1.col1, mv2.col4, mv3.col2
FROM mv1, mv2, mv3
WHERE mv1.id = mv2.id
  AND ...

Report message to a moderator

Re: Replication [message #359120 is a reply to message #359119]

Thu, 13 November 2008 15:33

babuknb
Messages: 1734
Registered: December 2005
Location: NJ

Senior Member

>>CTAS do the job

CTAS Mean???

Babu

[Updated on: Thu, 13 November 2008 15:34]

Report message to a moderator

Re: Replication [message #359122 is a reply to message #359120]

Thu, 13 November 2008 15:55

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

Senior Member
Account Moderator

CREATE TABLE AS SELECT ...

Also see CTAS

Report message to a moderator

Re: Replication [message #359123 is a reply to message #359122]

Thu, 13 November 2008 16:01

babuknb
Messages: 1734
Registered: December 2005
Location: NJ

Senior Member

Thanks Littlefoot

Babu

Report message to a moderator

Re: Replication [message #359278 is a reply to message #359123]

Fri, 14 November 2008 08:59

smunir362
Messages: 306
Registered: September 2007

Senior Member

Sorry for uncomplete requirements.
Actual requirements are as follows.

Materialized Views (MV1, MV2, MV3) these are on commit refresh.
Now I want a single materliazed view (some column from MV1, some from MV2 and some from MV3). But this materliazed view whould be insertable.

Report message to a moderator

Re: Replication [message #359314 is a reply to message #359278]

Fri, 14 November 2008 12:48

babuknb
Messages: 1734
Registered: December 2005
Location: NJ

Senior Member

I think your going to create materialized view contain more then one materialized view.

It's should be read only not updatable.

Babu

Report message to a moderator

Re: Replication [message #359416 is a reply to message #359278]

Sat, 15 November 2008 13:59

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

Senior Member
Account Moderator

So, you'd like to insert into a materialized view based on three materialized views which are based on certain tables (probably)?

Check what Materialized View Concepts and Architecture document says about read-only/updatable/writeable materialized views.

Just wondering: if there were NOT NULL table columns which are NOT part of the three materialized views, how do you plan to avoid the "can not insert null into ..." error?

Report message to a moderator