Home » Server Options » Replication » Materialized view
Materialized view [message #560154] Wed, 11 July 2012 00:29 Go to next message
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.
Re: Materialized view [message #560172 is a reply to message #560154] Wed, 11 July 2012 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Materialized view [message #560177 is a reply to message #560172] Wed, 11 July 2012 01:50 Go to previous messageGo to next message
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.
Re: Materialized view [message #560179 is a reply to message #560177] Wed, 11 July 2012 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Materialized view [message #560201 is a reply to message #560179] Wed, 11 July 2012 04:15 Go to previous messageGo to next message
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.
Re: Materialized view [message #560204 is a reply to message #560201] Wed, 11 July 2012 04:26 Go to previous messageGo to next message
moulshree
Messages: 13
Registered: July 2012
Junior Member
To add: Table doesn't have any primary key
Re: Materialized view [message #560208 is a reply to message #560204] Wed, 11 July 2012 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you should read the links I pointed you too to know what "post it formatted" means.

Regards
Michel
Re: Materialized view [message #560222 is a reply to message #560208] Wed, 11 July 2012 05:33 Go to previous messageGo to next message
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.
Re: Materialized view [message #560225 is a reply to message #560222] Wed, 11 July 2012 05:58 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Meterialized view help
Next Topic: Materialized view creation Error
Goto Forum:
  


Current Time: Thu Mar 28 12:48:07 CDT 2024