Home » Server Options » Replication » Oracle Materialized View | Deletion of Records, Oracle Materialized View | Deletion of Records (merg
Oracle Materialized View | Deletion of Records, Oracle Materialized View | Deletion of Records (merg [message #430911] Fri, 13 November 2009 01:22 Go to next message
appasamy
Messages: 1
Registered: November 2009
Junior Member
One question reg Materialized views.

If as part of housekeeping of the Source database we delete some records (older records), will the materialized view also be updated with the deletion?

I believe the answer is yes. In that case can we ensure that this delete does not happen?

Is there anyway we can prevent MView refresh from deleting the records that is once inserted even if we delete the same records in source DB?
Re: Oracle Materialized View | Deletion of Records, Oracle Materialized View | Deletion of Records [message #430917 is a reply to message #430911] Fri, 13 November 2009 02:23 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Records will be removed from a materialized view upon the first refresh.

If you don't want that to happen, never refresh it again.

Or, create an "archive" table (now that you still have records in the materialized view) using CTAS:
create table archive_table as
select * from materialized_view
If you refresh the MV now, you'll still have copy of the deleted records in the archive table.
Re: Oracle Materialized View | Deletion of Records, Oracle Materialized View | Deletion of Records [message #430919 is a reply to message #430911] Fri, 13 November 2009 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If as part of housekeeping of the Source database we delete some records (older records), will the materialized view also be updated with the deletion?

Yes. (Just try it.)

Quote:
In that case can we ensure that this delete does not happen?

You can't with MVIEW. You can if you use Streams instead.

Regards
Michel

Re: Oracle Materialized View | Deletion of Records, Oracle Materialized View | Deletion of Records [message #430920 is a reply to message #430911] Fri, 13 November 2009 02:34 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT multipost your question.

Regards
Michel
Previous Topic: Multi master replication issue
Next Topic: (materialized view) Replication Oracle - MS SQL Server
Goto Forum:
  


Current Time: Thu Mar 28 17:11:38 CDT 2024