OraFAQ Forum: Replication » Materialized View log Purging and Use of sequence Id (merged 3)

Home » Server Options » Replication » Materialized View log Purging and Use of sequence Id (merged 3) (Oracle 11g)

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

Materialized View log Purging and Use of sequence Id (merged 3) [message #606085]

Sun, 19 January 2014 12:06

kgecdeep
Messages: 8
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 ?

Report message to a moderator

Re: Materialized View log Purging and Use of sequence Id [message #606090 is a reply to message #606085]

Sun, 19 January 2014 13:06

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

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.

Report message to a moderator

Re: Materialized View log Purging and Use of sequence Id [message #606162 is a reply to message #606090]

Mon, 20 January 2014 10:59

kgecdeep
Messages: 8
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

Report message to a moderator

Re: Materialized View log Purging and Use of sequence Id [message #606164 is a reply to message #606162]

Mon, 20 January 2014 11:03

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

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.

Report message to a moderator

Re: Materialized View log Purging and Use of sequence Id [message #606179 is a reply to message #606164]

Mon, 20 January 2014 22:21

kgecdeep
Messages: 8
Registered: April 2012
Location: kolkata

Junior Member

Thanks Michel , Its really helpful . Thanks a lot

Report message to a moderator

MV log purging or archiving [message #606487 is a reply to message #606085]

Fri, 24 January 2014 10:39

kgecdeep
Messages: 8
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.

Report message to a moderator

Re: MV log purging or archiving [message #606489 is a reply to message #606487]

Fri, 24 January 2014 10:59

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

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?

Report message to a moderator

MVLog Purge [message #613932 is a reply to message #606085]

Tue, 13 May 2014 11:02

kgecdeep
Messages: 8
Registered: April 2012
Location: kolkata

Junior Member

Hi,

I am using MVLog table for one my ETL job to track the delta changes. The MV Log is having Sequence$$ which is a key field in my ETL job. The MVLog has become very big and I want to delete the old records from the MLOG below certan SEQUENCE$$ only ... but I heard that deleting below certain Sequence$$ will not help performance wise much since the blocks will be still there and the table size will not change much since we are deleting only records.

In that case my select from MVLog table will not improve since it will still access a very large table . Please share your thoughts or any option of purging below certain sequence$$ so that we can make the tabe size less

Report message to a moderator

Re: MVLog Purge [message #614032 is a reply to message #613932]

Wed, 14 May 2014 12:53

kgecdeep
Messages: 8
Registered: April 2012
Location: kolkata

Junior Member

Please help on the above issue

Report message to a moderator

Re: MVLog Purge [message #614033 is a reply to message #614032]

Wed, 14 May 2014 13:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post actual SQL & EXPLAIN PLAN for what you report as being slow

Report message to a moderator

Re: MVLog Purge [message #614035 is a reply to message #613932]

Wed, 14 May 2014 15:06

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

Senior Member
Account Moderator

Continuing your previous topic(s). I merge with them.

Report message to a moderator

Re: MVLog Purge [message #614041 is a reply to message #614035]

Wed, 14 May 2014 23:31

kgecdeep
Messages: 8
Registered: April 2012
Location: kolkata

Junior Member

I can track the sql directly since it is maintained in ETL job dataflow. I saw there is no index on sequence$$ , so everytime the select query is ding a full table scan and even i delete the old records from MV Log table below certain sequence$$ and makes the MVLog smaller in terms of records , still the table size is huge and the select query does a full table scan , which I think does nt improve performance even after deleting records. Is there any way I can use the Purge procedure of MVLog using a sequence$$ parameter so that procedure will do the needful and it may help?

Report message to a moderator

Re: MVLog Purge [message #614042 is a reply to message #614041]

Wed, 14 May 2014 23:59

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

Senior Member
Account Moderator

Quote:

I saw there is no index on sequence$$

If you need it then create it.

Report message to a moderator

Previous Topic:	How to skip comments in logical standby?
Next Topic:	Database refresh using different tools

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 11:59:14 CST 2025