DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: MLOG$ tables

  1. #1
    Join Date
    May 2004
    Posts
    29

    MLOG$ tables

    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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Read the manuals about snapshots/materialized views and about what is needed on their master tables to perform fast refresh.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    May 2004
    Posts
    29

    Question

    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

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    May 2004
    Posts
    29
    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

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Having another replicated table registered against the referenced table, and not refreshing it, might do the trick.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Apr 2003
    Posts
    26
    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.
    Tarek Al'Kaddoumi ...

  8. #8
    Join Date
    May 2004
    Posts
    29
    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

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    May 2004
    Posts
    29

    Thumbs up

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

    regards,
    Prasenjit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width