No question at this time
DBA Top 10
1 A. Kavsek 8700
2 B. Vroman 6600
3 M. Cadot 5600
4 T. Boles 4550
5 P. Wisse 4100
6 J. Schnackenberg 3700
7 G. Lambregts 1100
7 . Lauri 1100
7 T. P 1100
10 R. Pattyn 800
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48379
Total active users1533
Act. users last 24h5
Act. users last hour0
Registered user hits last week231
Registered user hits last month580
Go up

ORA-01555: snapshot too old -- When querying Materialized views with CLOB data
Next thread: How to change disconnect:true to disconnect:false
Prev thread: Will be possible to patch standby first and prod after one week??

Message Score Author Date
Users are coming across ORA-1555 errors when query...... raghavendra rao yella Jan 11, 2019, 15:56
Hi, please read the MOS 1950896.1 - "IF: ORA-15...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Ales Kavsek Jan 12, 2019, 01:04

Follow up by mail Click here


Subject: ORA-01555: snapshot too old -- When querying Materialized views with CLOB data
Author: raghavendra rao yella, United States
Date: Jan 11, 2019, 15:56, 156 days ago
Os info: Solaris 10
Oracle info: 11.2.0.3
Error info: ORA-01555: snapshot too old: rollback segment number with name "" too small~ORA-22924: snapshot too old
Message: Users are coming across ORA-1555 errors when querying data against materialized views with CLOB data.

In the past they were able to pin point to specific rows..
Came across forums saying corrupted clob fields results in ORA-1555

I had to perform complete refresh of materialized view as a work around. We can't perform complete refresh of this table everytime ora-1555 shows up.

Any thoughts on why this is occurring and how to get rid of these?

ORA-01555: snapshot too old: rollback segment number with name "" too small


Thanks as always...
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01555: snapshot too old -- When querying Materialized views with CLOB data
Author: Ales Kavsek, Slovenia
Date: Jan 12, 2019, 01:04, 156 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi,

please read the MOS 1950896.1 - "IF: ORA-1555 on LOB Data".
LOB corruption is indeed one possible reason (in which case full refresh is your only friend;-), but I don't think this is the issue in your case, since you're receiving this error consistently and I don't believe you're so unlucky :-).

The second reason mentioned in the note are inadequate PCTVERSION/RETENTION attributes of the LOB segments. This sounds more plausible as a reason, since undo for the LOB is not stored in UNDO tablespace but in a LOB segment itself. Try to increase the values of retention/pctversion as suggested in the note.

And please, let us know if this fixed your problem with ORA-01555 querying LOB column.

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here