Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    8

    Unanswered: empty tables after a complete refresh

    Hello,

    I have two databases connected by VPN.

    The tables of the destiny database are materialized views of the
    source database. The destiny's tables are refreshed once a day using a complete refresh.

    Some days ago the connection between the databases broke down during the refresh process. As a result there were empty tables in the destiny's database.

    I think that the only reason to explain this can be that the process first removes all the data from the table and then copies all the new data, and the process died before completing the task.

    Is this possible? Doesn't Oracle do an automatic rollback if it's impossible to update the materialized views?

    Thanks in advance,

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I believe that a complete refresh does a TRUNCATE of the table prior to inserting rows.... Truncate is not a logged trx; therefore can't be rolled back.

    Gregg

  3. #3
    Join Date
    Sep 2003
    Posts
    8
    thank you Gregg,

    so, is there a way to avoid having empty tables in case the connection breaks down?

    thanks again,

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Here's a couple of queries I use to keep up with snaps...

    ------------------------------------------------------------
    ------- Shows which Snapshots need manual refresh -------
    ------------------------------------------------------------

    --- Fast Refresh Problems ---
    set linesize 95
    select substr(s.name,1,20),error,refresh_method,fr_operat ions "Fast Refresh",
    cr_operations "Complete Refresh",type,
    to_char(t.last_refresh,'DD-MON-YY HH24:MIS') "Next Refresh"
    from all_snapshots s, all_snapshot_refresh_times t
    where fr_operations <> 'VALID'
    -- and type = 'FAST'
    and s.name = t.name
    order by t.last_refresh desc
    ;
    set linesize 80

    --- Complete Refresh Problems ---
    set linesize 95
    select substr(s.name,1,20),error,refresh_method,fr_operat ions "Fast Refresh",
    cr_operations "Complete Refresh",type,
    to_char(t.last_refresh,'DD-MON-YY HH24:MIS') "Next Refresh"
    from all_snapshots s, all_snapshot_refresh_times t
    where cr_operations = 'REGENERATE'
    and type = 'COMPLETE'
    and s.name = t.name
    order by t.last_refresh desc
    ;
    set linesize 80


    HTH
    Gregg

Posting Permissions

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