materialized view updates
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: materialized view updates

  1. #1
    Join Date
    Oct 2002
    Posts
    9
    I'm having trouble with getting a materialized view with fast refresh to update. I'm working between two schemas and have the global query rewrite privilege for both users. I've run the catrep.sql script as system and as sys to make sure it had been run and find that it has a lot of packages that don't compile, which I guess is expected as I'm not using Advanced Replication, just mviews. I think the problem is that I can't get the dbms_snapshot package to compile so can't even update the view manually. Is there another script I need to run to make the materialized view updates to work? Some other privilege or variable?
    I've granted permissions on tables, views, materialized views, triggers, and snapshots to both users.
    The log does get entries but never sends them to the views.

    I have a base table and a log on the table in schema 1.
    I have a materialized view in schema 2: (this is in preparation for having the base table and view in two separate databases and versions of Oracle.)

    create materialized view log on schema1.document
    tablespace app_mview
    with primary key
    ( list columns needed);

    create materialized view schema2.doc_mv
    pctfree 0
    tablespace app_mview
    storage (initial 128k next 128k pctincrease 0)
    refresh fast
    start with sysdate
    next sysdate + 1/1440
    as select * from schema1.document

    Does anyone know where my problem might be?
    thanks for any help

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I'm in the dark about why it won't fast refresh, but let me just point out that "next sysdate + 1/1440" will not cause a refresh every one minute -- the next refresh will be one minute after the last refresh ended, so if the refresh takes a couple of seconds your refresh times will "creep". Apologies if you already knew this.

  3. #3
    Join Date
    Oct 2002
    Posts
    9
    Thanks, I figured it would but thought I'd fix that once I got the process working. It actually wouldn't matter if it crept in this instance as the near-real-time is the more important issue. Only if it "steps on itself" and can't ever complete would there be an issue. There'll be 10's of thousands of updates per day so I figured smaller, more frequent updates would be better. I appreciate the input though.

  4. #4
    Join Date
    Sep 2002
    Posts
    42
    Just a small guess

    change the refresh fast clause in materialized view to
    refresh fast with primary key

    and try now


    Cheers
    In GOD I find everything

  5. #5
    Join Date
    Oct 2002
    Posts
    9
    I have found out that I'm having another problem that may be the base of my whole issue. I have found that dbms_job cannot do the refresh because the dbms_snapshot package is not valid. I cannot get snapshot to compile at all so figure there must be missing pieces in my install somewhere (except I didn't do the install). I've seen a reference that the distributed option must be installed for snapshots to work, but also saw that should be part of the 8.1.7 server options so it acutally should have been installed automatically. The table referenced to check for this is V$XATRANS$ and is not in my configuration however. I must be missing a script or something since basic snapshot replication should be available without any of the advanced replication features.

    Does anyone have an idea why dbms_snapshot is invalid and how to fix it? I don't want to indescriminately run scripts as I'm working on the same machine as other developers.
    thanks for any input, I'm getting desparate. Deadlines approaching!

  6. #6
    Join Date
    Sep 2002
    Posts
    42
    respected sir,

    you try to compile the dbms_snapshot and show err

    after you see the error..You'll have some idea what you missed..

    Otherwise you send me the info
    connect as sys

    sql>alter package dbms_snapshot compile

    you will get

    package compiled with errors

    sql>show err

    after this you send the info

    I'll tell you what script you missed

    In GOD I find everything

  7. #7
    Join Date
    Oct 2002
    Posts
    9
    Using a sho err when logged in as my user or as sys returns "no errors". When I check the dba_errors table the only thing that shows is that the dbms_snapshot package is invalid. The error log shows only that the dbms_snapshot package is invalid. There are no other errors showing up when trying to execute snapshot or exectuting the job to run the refresh.

    I have however, also discoverd that the oemapp console/dbastudio/etc do not work either, nor does oemctrl start oms (starts out then hangs).
    I think there's something wrong with this install! But, I need to be able to explain what's wrong before getting someone to agree to reinstall, otherwise the install will be the same.

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