Home » RDBMS Server » Backup & Recovery » dbms_snapshot.refresh
dbms_snapshot.refresh [message #570448] Fri, 09 November 2012 13:34 Go to next message
Messages: 1
Registered: November 2012
Location: Piketon, Ohio
Junior Member
DBA is running dbms_snapshot.refresh every 30 minutes looking at 3 systems. Worked fine when all three systems were in one time zone. Now, two systems are in Eastern and one is in Central time zone. Almost daily, the job will start but will not finish before the next is scheduled to run, thus each subsequent job never starts and manual intervention is required. DBA is searching for answers elsewhere...I am merely posting this in an effort to help her. Any ideas...if nothing else is there a way to kill any job that runs beyond 29 minutes? Thanks
Re: dbms_snapshot.refresh [message #570463 is a reply to message #570448] Sat, 10 November 2012 00:49 Go to previous messageGo to next message
Messages: 21759
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not a DBA, I have never worked in multiple time zones so - this is just pure theory, most probably wrong.

Anyway, this is how I see it: I have a database in Europe and a snapshot object. You have a database in Ohio, and it contains a table that is a source for my snapshot. Then, I say REFRESH! and the process starts. Now, what difference does it make whether your database is in Ohio, in Australia, on a sever next to me or wherever else?

Could it be that the source table size is much larger now and it takes more than 30 minutes to refresh it? What happens if you schedule refresh job to, say, 60 minutes?
Re: dbms_snapshot.refresh [message #570468 is a reply to message #570448] Sat, 10 November 2012 01:42 Go to previous message
John Watson
Messages: 8804
Registered: January 2010
Location: Global Village
Senior Member
I would check whether the materialized view logs are being cleared out properly.
(This is not to do with timezones, just abother possibility.)
Previous Topic: ORA-19573: cannot obtain exclusive enqueue for datafile 1
Next Topic: sysaux datafile accidentally removed using rm command
Goto Forum:

Current Time: Sat Mar 25 02:39:49 CDT 2023