|refreshing snapshots [message #510702]
||Tue, 07 June 2011 05:37
Registered: August 2007
I am using Oracle 8i (8.1.7) and have created a snapshot as below
CREATE SNAPSHOT pfleave
START WITH '06-JUN-2011'
NEXT NEXT_DAY(TRUNC(SYSDATE), 'TUESDAY') + 12/24
WITH PRIMARY KEY
ihalfleaveavailed fhlffullava ,
from prime.prtrlvh@primelink where leavecode in ('31','11')
My problem is that the snapshot does not get refreshed automatically
on Tuesdays at 12.00 Hrs. I have assigned Global query rewrite priviledge to
AFRES the owner of the snapshot. Can anyone please help me.
|Re: refreshing snapshots [message #510707 is a reply to message #510702]
||Tue, 07 June 2011 05:59
Registered: March 2007
Location: Nanterre, France, http://...
1/ '06-JUN-2011' is not a date but a string:|
SQL> CREATE SNAPSHOT pfleave
2 PCTFREE 10
3 PCTUSED 70
4 MAXTRANS 255
5 TABLESPACE afres
6 STORAGE (
7 INITIAL 57344
8 NEXT 57344
9 PCTINCREASE 0
10 MINEXTENTS 1
11 MAXEXTENTS 505
13 REFRESH COMPLETE
14 START WITH '06-JUN-2011'
15 NEXT NEXT_DAY(TRUNC(SYSDATE), 'TUESDAY') + 12/24
16 WITH PRIMARY KEY
17 FOR UPDATE
19 select * from t;
select * from t
ERROR at line 19:
ORA-00604: error occurred at recursive SQL level 1
ORA-01858: a non-numeric character was found where a numeric was expected
2/ Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
3/ Please Use SQL*Plus and copy and paste your session, the WHOLE session, including snapshot creation and result of "select * from dba_snaphots where name=..." and "select * from v$version".
4/ Post result of "show parameter job"