OraFAQ Forum: Replication » Materialized view

Home » Server Options » Replication » Materialized view

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

Materialized view [message #560154]

Wed, 11 July 2012 00:29

moulshree
Messages: 13
Registered: July 2012

Junior Member

Hi,
I have two databases one is 10g and the other is 11g. I did data replication using materialized view:
On 10 g:
CREATE MATERIALIZED VIEW LOG ON testing_mview WITH ROWID;
On 11g:
create materialized view testing_mview_1 REFRESH Force start with (sysdate) next (sysdate+1/1440) with rowid as select * from testing_mview@ORCL10R2
This is working. Now my requirement is I want columns sysdate and dmltype to be added to table testing_mview_1 on 11g.
Say for eg:
On 10 g: insert into testing_mview values('A','B','C');
testing_mview
col1 col2 col3
A B C
On 11g:
testing_mview_1
col1 col2 col3 sysdate dmltype
A B C 11/07/2012 I

Please let me know how to achieve this.

Report message to a moderator

Re: Materialized view [message #560172 is a reply to message #560154]

Wed, 11 July 2012 01:36

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is the third topic you created about link between 10g and 11g.
In the first 2 ones you got help but did neither feedback nor give the solution of your problems.
It is fair to give the solution of the problems when you find one.
You got help, it is your turn to help.
Waiting for these feedbacks before continuing to help you.

In addition, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

Re: Materialized view [message #560177 is a reply to message #560172]

Wed, 11 July 2012 01:50

moulshree
Messages: 13
Registered: July 2012

Junior Member

Hi Michael, I replied to one of the posts earlier , may be you didnt check. For the second one, I didnt find any successfull resolution but changed my way of doing it so I didnt reply. I have replied now to it also.

Report message to a moderator

Re: Materialized view [message #560179 is a reply to message #560177]

Wed, 11 July 2012 01:58

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is not clear what you have and want.
Post table definition and target materialized view definition.
And post it formatted.

Regards
Michel

Report message to a moderator

Re: Materialized view [message #560201 is a reply to message #560179]

Wed, 11 July 2012 04:15

moulshree
Messages: 13
Registered: July 2012

Junior Member

Hi Michael,
Operating system is Linux on both servers.
server 1:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

server 2:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

5 rows selected.

Table structure is huge. It has 48 columns. Table name is testing_mview. It is at server1 i.e. 10g
Created MV log on this table:

SQL> create materialized view log on testing_mview with rowid;

Materialized view log created.

Created MV on this table at server2 i.e. 11g:

SQL> create materialized view testing_mview_1 REFRESH Force start with (sysdate) next (sysdate+1/1440)with rowid as select * from testing_mview@ORCL10R2;
Materialized view created.

This MV like its master table has 48 columns. Now I want it to have 2 extra columns:

1) Entry_date: the system date and time at which data reached MV. For eg: insert was performed at 11:00 at 10g but it reached 11g at 12:00 due to link break. hence, Entry_date will be 12:00 in this case
2) Dmltype: whether the DML operation done on the row was an insert/ delete/ update.

Report message to a moderator

Re: Materialized view [message #560204 is a reply to message #560201]

Wed, 11 July 2012 04:26

moulshree
Messages: 13
Registered: July 2012

Junior Member

To add: Table doesn't have any primary key

Report message to a moderator

Re: Materialized view [message #560208 is a reply to message #560204]

Wed, 11 July 2012 05:01

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Maybe you should read the links I pointed you too to know what "post it formatted" means.

Regards
Michel

Report message to a moderator

Re: Materialized view [message #560222 is a reply to message #560208]

Wed, 11 July 2012 05:33

moulshree
Messages: 13
Registered: July 2012

Junior Member

I dont know what games you are playing with me. I think the details I provided explains the issue clearly. Leave it, I prefer utilizing the time searching the solution myself rather than reading formatting guides.

Report message to a moderator

Re: Materialized view [message #560225 is a reply to message #560222]

Wed, 11 July 2012 05:58

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I play no game with you, I pointed you many times to the guide and to how you have to post.
If you refuse to follow and behave with the minimum respect you must have you will have no more help.

Quote:

I prefer utilizing the time searching the solution myself rather than reading formatting guides.

And the guide mentions you should FIRST search and then post if you can't find.
So we are in agreement.

Regards
Michel

Report message to a moderator

Previous Topic:	Meterialized view help
Next Topic:	Materialized view creation Error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 21 06:44:24 CDT 2026