Home » Server Options » Replication » Materialized View log Purging and Use of sequence Id (merged) (Oracle 10g)
Materialized View log Purging and Use of sequence Id (merged) [message #606085] Sun, 19 January 2014 12:06 Go to next message
kgecdeep
Messages: 5
Registered: April 2012
Location: kolkata
Junior Member
Hi,

I have designed an ETL job where I am using the Materialized View log as my source for delta records extraction by using the DML_Type ( I, U, D) and sequence Id to track the latest changes.

I am capturing all the change records in a table by using the sequence number in the MV Log . Once the capture is done for the instance , I am tracking the maximum sequence ID and then the next instance will start from there. This is how I am using the sequencenId .

My concern is:

I have seen the sequence Id is always increasing , if that is the case my design will work. id it possible that the sequence Id will get reset at any stage , if yes how

Also My MV log is getting larger , I want to purge/archive the MV log in a regular frequency based on old sequence Ids that are already loaded and not required . How I can purge the MV log from below certain sequence Ids ?
Re: Materialized View log Purging and Use of sequence Id [message #606090 is a reply to message #606085] Sun, 19 January 2014 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you created you rmveiw log with SEQUENCE option then this sequence is retrieved from SYS.CDC_RSID_SEQ$ which is defined with increment by 1, order, no upper limit and no cycle option, so it can't decrease.

Re: Materialized View log Purging and Use of sequence Id [message #606162 is a reply to message #606090] Mon, 20 January 2014 10:59 Go to previous messageGo to next message
kgecdeep
Messages: 5
Registered: April 2012
Location: kolkata
Junior Member
Thanks . Please let me know if I can purge/archive the data from the MV log on monthly basis by using the sequence Id ? Lets say I want to purge the old data from MV log below certain sequence id as the old records are already processed and not required any more .. pls suggest
Re: Materialized View log Purging and Use of sequence Id [message #606164 is a reply to message #606162] Mon, 20 January 2014 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't use any Oracle feature with this mview log then you can delete the rows as you want.
If you want to do it in a month basis maybe it is better to use the SNAPTIME$$ column than the SEQUENCE$$ but it is up to you.

Re: Materialized View log Purging and Use of sequence Id [message #606179 is a reply to message #606164] Mon, 20 January 2014 22:21 Go to previous messageGo to next message
kgecdeep
Messages: 5
Registered: April 2012
Location: kolkata
Junior Member
Thanks Michel , Its really helpful . Thanks a lot
MV log purging or archiving [message #606487 is a reply to message #606085] Fri, 24 January 2014 10:39 Go to previous messageGo to next message
kgecdeep
Messages: 5
Registered: April 2012
Location: kolkata
Junior Member
I want to purge the MV log weekly basis or monthly basis and I have sequence Id column in the MV log. I am using the MV log as source for one of my ETL job.

I want to archive the MV log using the below query:

delete from Mlog$_EMP where sequence$$ < (max) sequence id that are already processed by ETL job. Is it ok if I use this type of direct delete statement using swquence$$ column in the where clause as I can track the records easily via this sequenceId which are no more required .

I have heard there is a oracle defined process to purge the MV log , procedure , if I ignore the same and use the above delete statement is it ok.
The MV log will be used by my ETL job only.
Re: MV log purging or archiving [message #606489 is a reply to message #606487] Fri, 24 January 2014 10:59 Go to previous message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Same answer as in your previous topic; why do you think it should be different? And why do you start a new topic for the same or very closely related question?

Previous Topic: Golden gate monitoring script for struck processess
Next Topic: Refresh Materialized Views which are dependent on others MV
Goto Forum:
  


Current Time: Thu Apr 24 15:47:38 CDT 2014

Total time taken to generate the page: 0.10319 seconds