MLOG$ tables

[prasen999]

Hi,
I want to have some detailed information regarding MLOG$_ tables - what are they , whether/how they can be created and how can it be of use to capture datachange in the table on which the MLOG$ tabke is created etc.

Thanks and regards,
Prasenjit

[jmodic]

Read the manuals about snapshots/materialized views and about what is needed on their master tables to perform fast refresh.

[prasen999]

hi,
thanks for the reply. actually i had also the knowledge that Mlog$ table is created in the case of primary key fast refresh materialized view when one creates the log on the master table.
but recently i got to hear that that mlog$ table can be created on any table and the changes in that table are stored as records in the corresponding mlo$ table.
in the net i found a definition like the following regarding creation of mlog$ table : -

CREATE TABLE "SCOTT"."MLOG$_EMP"
( "EMPNO" NUMBER(4,0),
"SNAPTIME$$" DATE,
"DMLTYPE$$" VARCHAR2(1),
"OLD_NEW$$" VARCHAR2(1),
"CHANGE_VECTOR$$" RAW(255) ) ;

so i had also created a similar table and expected to find a record when i change a record in my emp table. but that did not happen.

regards,
Prasenjit

[jmodic]

Well, creating a table will do nothing - Oracle will treat it just like any other table. In order this table to be populated you would have to create some triggers on the base table - that's what Oracle does when you create snapshot logs on base tables. However those triggers are "internalized" and I doubt you'll find the code for them.

[prasen999]

ok if that be so then is there any way that i can capture the data in the MLog$ table. because, i have seen while creating the materialized view that the Mlog$ table is purged whenever the refresh process completes and the change is transferred from the master to the repliacted table. the data stays for the interval during the refresh, isn't it? is there any way that i can control the purguing of the table.

regards,
Prasenjit

[slimdave]

Having another replicated table registered against the referenced table, and not refreshing it, might do the trick.

[tarek]

Hi..
About capturing data of MLOG$ table, and if you want to store all purged data from the snapshot log even after the last snapshot refresh, you can use triggers on the snapshot log, use Trigger After or Before Delete to insert your snapshot log data into another table.

HTH.

[prasen999]

guys ..good suggestions and good to see that many people are joining this discussion. tell me, how does the dba_snapshot_logs table get populated and is it possible to associate a normal db table with the dba_snapshot_logs table. i may really sound bugging, but my dba architect has given a solution based on this.

thanks and regards,
Prasenjit

[jmodic]

DBA_SNAPSHOT_LOGS is not a table, it's a view, just like any other DBA_* dictionary view. It doesn't "get populated" by anything, it's like any other view - it gets the results by executing it's definition query.

In this particular case, when you create snapshot logs on some any of your table, this get's written into some data dictionary tables that record the existance of snapshot logs. And when you query DBA_SNAPSHOT_LOGS view you are actualy querying those internal dictionary tables. And no, you can not "associate" an ordinary table with DBA_SNAPSHOT_LOG.

You realy seems very confused with all this snapshot logs stuff. What is it you actually want to acchive? Or is you database architect the one that is confused here?

[prasen999]

thanks a lot folks - I got answer to all my problems.

regards,
Prasenjit