-
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
-
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.
-
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.
-
Just a small guess
change the refresh fast clause in materialized view to
refresh fast with primary key
and try now
Cheers
-
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!
-
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
-
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.
