ORA-01555: snapshot too old -- When querying Materialized views with CLOB data
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, 4 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, 4 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