OraFAQ Forum: Replication » refreshing snapshots

Home » Server Options » Replication » refreshing snapshots (oracle 8i 8.1.7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

refreshing snapshots [message #510702]

Tue, 07 June 2011 05:37

alister
Messages: 101
Registered: August 2007
Location: India

Senior Member

Hi everybody
I am using Oracle 8i (8.1.7) and have created a snapshot as below

CREATE SNAPSHOT pfleave
PCTFREE 10
PCTUSED 70
MAXTRANS 255
TABLESPACE afres
STORAGE (
INITIAL 57344
NEXT 57344
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 505
)
REFRESH COMPLETE
START WITH '06-JUN-2011'
NEXT NEXT_DAY(TRUNC(SYSDATE), 'TUESDAY') + 12/24
WITH PRIMARY KEY
FOR UPDATE
AS
select empno,leavecode,leaveyear,
ihalfleaveaccumulated fhlfacc,
ihalfleaveeligible fhlfeli,
ihalfleaveavailed fhlffullava ,
iihalfleaveaccumulated shlfacc,
iihalfleaveeligible shlfeli,
iihalfleaveavailed shlffullava
from prime.prtrlvh@primelink where leavecode in ('31','11')
and leaveyear>'2009'

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.

Thank you

Alister

Report message to a moderator

Re: refreshing snapshots [message #510707 is a reply to message #510702]

Tue, 07 June 2011 05:59

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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
 12  )
 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
 18  AS
 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"

Regards
Michel

Report message to a moderator

Previous Topic:	Golden Gate Login Oddities
Next Topic:	Materialized View Not Refreshing Automatically

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Mar 26 23:45:42 CDT 2023